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

grazitti_sapna's avatar
grazitti_sapna
Super User
1 year ago

Hi _bs_ ,

 

Follow below steps to achieve this task:

 

Create a Disconnected table manually in Power BI (or in SSAS if needed), use below DAX

 

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

Add above as a slicer with multi-select (check box enabled)

 

Create Individual Measures for Each Metric

Examples 

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

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

-- Repeat for each metric

Now add the created measures to a table visual as required

 

Create a measure to only show the required columns 

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

Apply visible level filter to show only desired measures in the table by adding Measure(Visible Rows) as a visual level filter and select visual rows = 1

 

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

_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