Allows users to select multiple measures

[_bs_]

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?

E.g, Months, Profit, Revenue, Qty

One issue, is that my data source is a SSAS Tabular Model, therefore a live connection which rules out the Field Parameter feature within Power BI.

At the moment I have managed to create a measure selection slicer based on the SWITCH() function with a static measures table, but it can only display 1 measure at a time.

So, looking for multi-select here.

Any suggestions please.

Thank you.

[v-pgoloju]

Hi @_bs_ 

Just following up to see if the response provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

Best regards,

Prasanna Kumar

[v-pgoloju]

Hi @_bs_,

Just following up to see if the solutions provided  were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Regards,

Prasanna Kumar

[_bs_]

Hello @v-pgoloju - I have implemented your suggestion.

As you have mentioned, we cannot dynamically hide columns which is what I was looking for.

Making columns show blank values somewhat works, but the measure name is visible and when you export to excel, it will show all columns whether the values are visible or not.

I think this is far as we can go.

Many thanks for the support and this is great forum, as it's a great way to learn from others. 😀

[v-pgoloju · Accepted solution]

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]

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_]

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]

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_]

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