Query Regarding Dynamic Queries

[ANVS]

Hello There,

I have scenario which is as follows:

The requirement is that - There is a slicer which contains a dropdown consisting of values PC and UC.

In the Data Pane I have PC Calculated measures and UC calculated measures seperately around 50 to 60 measures for each.

So, now the requirement is that When I select UC from the Slicer Dropdown all my visuals, charts, KPI's, tables should take values from UC Calculated measures present in the data pane, and when I select PC from slicer dropdown all my visuals, KPI's, Tables, charts should take values from PC calculated measures present in the data pane.

So, how do I achieve this requirement in Power BI without much much complexity.

Appreciate an quick response.

[alish_b · Accepted solution]

Hey @ANVS ,

You could set up calculation groups with two calculation items for PC and UC. Then, the calculation items you could switch between measure names as follows:

PC = SWITCH(

    SELECTEDMEASURENAME(),

    "Revenue", [PC_Revenue],

    "Profit", [PC_Profit],

    "Units", [PC_Units],

    "Margin", [PC_Margin],

    "Avg Order Value", [PC_AvgOrderValue],

    BLANK()

)

Well here is where you would keep the 50-60 measures you have for PC and in another calculation item for UC you would keep the ones for UC. Common measures will share the same name. For the sake of clean implementation, you could add generic measures like Revenue = BLANK() and the calculation groups will handle replacing that with the required value when you make the calculation group choice. Here are some screenshots of a sample implementation:

Basically whenever it finds a measure named Revenue it swaps it with UC_Revenue when you have UC selected. And similar for PC.

Hope it helps!

[V-yubandi-msft]

If the response was helpful and resolved your issue, kindly consider marking it as the Accepted Solution. This makes it easier for other community members to discover useful answers and benefits everyone facing similar challenges.

Thank you

[V-yubandi-msft]

Hi @ANVS ,
Could you let us know if your issue has been resolved or if you are still experiencing difficulties? Your feedback is valuable to the community and can help others facing similar problems.

[ANVS]

Yes, Resolved

[V-yubandi-msft]

Thank you for confirming, @ANVS . If you can, please mark the response that resolved your issue. This will help others in the community find solutions more easily.

Regards,
Yugandhar.

[Shahid12523 · Accepted solution]

- ✅ Create a disconnected table with values PC and UC
- ✅ Add a slicer using that table
- ✅ Write a dynamic measure like this:

Dynamic_Measure =
SWITCH(
SELECTEDVALUE(MeasureSelector[Type]),
"PC", [PC_Measure],
"UC", [UC_Measure]
)

Use Dynamic_Measure in all visuals instead of separate PC/UC measures. Clean, scalable, and no duplication needed.

[ANVS]

Thanks for this @Shahid12523 .

Have a query here again:

Dont we need to create seperate dynamic measures for each measure in UC and PC calculations?

or only the Single measure which you have given works?

[V-yubandi-msft]

If you want to avoid having to create and manage 50,60 dynamic measures, the calculation group approach mentioned earlier is recommended. Calculation groups allow you to use generic measures in your visuals, and the group will automatically switch between PC and UC versions, making the solution more scalable.

regards,
Yugandhar.

[alish_b · Accepted solution]

Hey @ANVS ,

You could set up calculation groups with two calculation items for PC and UC. Then, the calculation items you could switch between measure names as follows:

PC = SWITCH(

    SELECTEDMEASURENAME(),

    "Revenue", [PC_Revenue],

    "Profit", [PC_Profit],

    "Units", [PC_Units],

    "Margin", [PC_Margin],

    "Avg Order Value", [PC_AvgOrderValue],

    BLANK()

)

Well here is where you would keep the 50-60 measures you have for PC and in another calculation item for UC you would keep the ones for UC. Common measures will share the same name. For the sake of clean implementation, you could add generic measures like Revenue = BLANK() and the calculation groups will handle replacing that with the required value when you make the calculation group choice. Here are some screenshots of a sample implementation:

Basically whenever it finds a measure named Revenue it swaps it with UC_Revenue when you have UC selected. And similar for PC.

Hope it helps!

[ChielFaber]

You might want to look into the usage of PowerBI bookmarks to create buttons to easily switch between the two options: 

An example video:
https://www.youtube.com/watch?v=_HTF7Ph7Eqc

Information:

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-bookmarks