Forum Discussion

_bs_'s avatar
_bs_
Advocate I
1 year ago
Solved

Allows users to select multiple measures

Hi, want to know if is possible to design a report where the user can pick 2 or more measures which is added to a Table visual. And even extend it to allow them to pick the columns/fields as well? ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi _bs_,

     

    Thank you for reaching out to the Microsoft Fabric Forum Community.

    MSelector = DATATABLE(
    "MeasureName", STRING,
    {{"Revenue"},
        {"Profit"},{"Qty"},{"Cost"}})

Selected_Revenue = 
IF (CONTAINS(ALLSELECTED(MeasureSelector), [MeasureName], "Revenue"),
    [Revenue], BLANK())

Selected_Profit = 
IF (CONTAINS(ALLSELECTED(MeasureSelector), [MeasureName], "Profit"),
    [Profit], BLANK())

Selected_Qty = 
IF (
    CONTAINS(ALLSELECTED(MeasureSelector), [MeasureName], "Qty"),
    [Qty],BLANK())

Selected_Cost = 
IF (
    CONTAINS(ALLSELECTED(MeasureSelector), [MeasureName], "Cost"),
    [Cost], BLANK())

Visible Rows = 
IF (
    NOT ISBLANK([Selected_Revenue]) ||
    NOT ISBLANK([Selected_Profit]) ||
    NOT ISBLANK([Selected_Qty]) ||
    NOT ISBLANK([Selected_Cost]),1,0)

    To enable users to multi-select and display only specific measures in a table visual in Power BI using a live SSAS Tabular connection (where Field Parameters are unavailable), the best workaround is to create a disconnected table with measure names and then use individual DAX measures that return values only when selected. While the table visual cannot dynamically hide columns, you can display blank values for unselected measures and control visible rows. Create a disconnected table named MeasureSelector using DATATABLE, and then define one measure per metric as shown below. Add these to the table visual, and use a Visible Rows measure as a visual-level filter to show only rows with selected data.


    Best regards,
    Prasanna Kumar

_bs_'s avatar
_bs_
Advocate I
1 year ago

Thank you grazitti_sapna - I have applied those steps.

It seems the table visual will show all the measure by default and when a measure is chosen in the slicer, it will then show the value whilst making the others blank.

E.g. Selected Cost from the list, which shows Cost value & Hides the Revenue.

 

However, I was expecting it to show the chosen measure only (Cost) as eventually I could have 20 measures, which will make the table wide. Is this possible to do?

Thanks

grazitti_sapna's avatar
grazitti_sapna
Super User
1 year ago

Hi _bs_ ,

 

Try Below DAX:

 

Selected_Revenue =
IF (
SELECTEDVALUE(MeasureSelector[MeasureName]) = "Revenue"
|| CONTAINS(ALLSELECTED(MeasureSelector), [MeasureName], "Revenue"),
[Revenue],
BLANK()
)

Selected_Profit =
IF (
SELECTEDVALUE(MeasureSelector[MeasureName]) = "Profit"
|| CONTAINS(ALLSELECTED(MeasureSelector), [MeasureName], "Profit"),
[Profit],
BLANK()
)

Selected_Qty =
IF (
SELECTEDVALUE(MeasureSelector[MeasureName]) = "Qty"
|| CONTAINS(ALLSELECTED(MeasureSelector), [MeasureName], "Qty"),
[Qty],
BLANK()
)

Selected_Cost =
IF (
SELECTEDVALUE(MeasureSelector[MeasureName]) = "Cost"
|| CONTAINS(ALLSELECTED(MeasureSelector), [MeasureName], "Cost"),
[Cost],
BLANK()
)

  • _bs_'s avatar
    _bs_
    Advocate I
    1 year ago

    Hi grazitti_sapna - that returns an error, does it need to be wrapped in a nested IF statement?

     

    And I've modified it to this:

    Selected Measure:=
IF(
IF (
SELECTEDVALUE(Options[Select Measure]) = "Revenue"
|| CONTAINS(ALLSELECTED(Options[Select Measure]), [Select Measure], "Revenue"),
[Revenue],
BLANK()
),
IF (
SELECTEDVALUE(Options[Select Measure]) = "Profit"
|| CONTAINS(ALLSELECTED(Options[Select Measure]), [Select Measure], "Profit"),
[Profit],
BLANK()
)
)

    which returns the value of the first measure, but how would it work with the Visible Rows measure/slicer?

    Thanks