Reproducing Primavera Select Columns in PowerBI; aka Dynamically adding measures to a Matrix

Edit: 16 may 2022, the hack is no more required, PowerBI released fields parameter that support this functionality out of the box.

TLDR, The online report is here

Primavera P6 calculated a lot of rich metrics for a schedule, we all know the basics, Start Date and Finish date, but there are all kinds of other values ( Cost, Labor hours, not Labor , Float Duration etc)

The challenge is, different people want to see different measures,  and once you publish your reports, the viewer can not change the visual, Microsoft is working on this feature, but it will take another couple of months to be released.

The trick was suggested by Kasper de Jonge and in a random chat with @DingbatData @_Ivan_Bond , they use it already to solve similar issues, actually it is very easy

Anyway the purpose to reproduce something like this from Primavera

1. Create a new table that contains all the measures

Instead of copy and past all the measures, I just used DAX studio connected to my PowerBI desktop to generate a list of all the measures, read this link

The Category is to just to make it easy to select which measure to select, the index is to keep the same sort in the slicer, I don’t want to show actual Finish before Actual Start.

2- Create a Master Measure that check if the value is selected.

Using Switch and SelectedValue give the result, I had only to add some condition to format the results of Date to be show as date not Number, you can vote on this idea

Here is a snapshot for the first 4 measures ( currently I have 29 in the models)

Selected_Measure =
SWITCH (
    SELECTEDVALUE ( dummy_meaures[MEASURE_Values], BLANK () ),
    “Task_Count”, [Task_Count],
    “Budget Labor Units”, [Budget Labor Units],
    “Actual Finish”, SWITCH (
        [Actual Finish],
        BLANK (), BLANK (),
        FORMAT ( [Actual Finish], “mm-yy-yy” ),

    “%_Labor_units”, SWITCH (
        [%_Labor_units],
        BLANK (), BLANK (),
        FORMAT ( [%_Labor_units], “0.00%” )
    ))

3- Add the column Masure_Value to the column in the Matrix and Seletced_value in the values Area.

4-Voila

5-Bonus

I am using bookmarks to toggle the select Columns Button, I think it is freaking cool,  hopefully in the short term PowerBI add more features so we can build not only dashboard but real application interface.