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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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_'s avatar
_bs_
Advocate I
1 year ago

Hello Anonymous - 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. ðŸ˜€