Forum Discussion
Advice on optimising SWITCH or alternative approach
We make dashboards that track multiple parameters on a list of items for a number of models with several measures. We have a table that groups the items by model and shows several measures for a selected parameter.
A simplified pbx file here: Test.pbix
Our approach was to unpivot the parameter columns. But we have a dashboard now for 2M items and 200 parameters, a table of 400M rows does not seem workable anymore (or does it?), so we kept the parameters in columns and use SWITCH now. For instance:
# Parameter Required =
VAR _selectedparameter=SELECTEDVALUE('Parameters'[Parameters])
VAR _filter =
FILTER( Input,
VAR parametercolumn =
SWITCH( TRUE(),
_selectedparameter = "Parameter1", Input[Parameter1],
_selectedparameter = "Parameter2", Input[Parameter2],
_selectedparameter = "Parameter3", Input[Parameter3],
_selectedparameter = "Parameter4", Input[Parameter4],
_selectedparameter = "Parameter5", Input[Parameter5]
)
RETURN
parametercolumn <> "N/A"
)
RETURN
CALCULATE([# Items], _filter)
In other measures we copy this SWITCH segment.
This works, but we are wondering:
* Is there a way to maintain the list of conditions in the SWITCH statement outside of the measures, so we can make changes in one place?
* Is there maybe another approach that would be more efficient to achieve the same?
* Or would unpivoting still be the way to go? Are there limits to take into account when unpivoting?
Thanks. We looked further into calculation groups but in this case it seems to just make things needlessly more complex and more difficult to maintain. From further research it seems we should try to stick with the unpivot approach anyway, so we'll focus on comparing performance with the switch approach.
5 Replies
v-veshwara-msftCommunity Support
HI styxbe ,
Thanks for posting in Microsoft Fabric Community.Unpivoting is a workable option if the model is well designed. Even with more rows (for example, 2 million items × 200 parameters), Power BI can handle this if you're using a star schema, applying filters correctly, and using summary tables where needed. This also gives more flexibility for visuals and DAX.
Power BI doesn’t currently support defining the SWITCH logic in one place and reusing it across multiple measures. One option to avoid repeating the same logic is using Calculation Groups, which let you apply shared logic across different measures.
If unpivoting isn’t possible right now, Calculation Groups can help reduce maintenance effort when working with many parameter columns.
To improve performance, you can also pre-aggregate key metrics like counts or sums per item and parameter or per model and parameter. This can be done using aggregation tables in Power BI with managed relationships, or by summarizing the data earlier using tools like Fabric before loading it into your model.
Here are some related discussions taht may help:
Solved: Alternatives to Unpivot - Microsoft Fabric CommunityUnpivot Multiple Sections of Data - Microsoft Fabric Community
Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to give a kudos and Accept as the solution to help the other members find it more quickly.
Thank you.- maruthisp
Super User
Hi styxbe,
Below are my findings or suggestions based on your post:Maintain the SWITCH logic -
No, Power BI DAX can’t natively refer to a central list of column references in a dynamic way. You must use explicit DAX, and there’s no “variable lookup” for column names.
There’s no “dictionary” or “mapping table” that can switch columns dynamically in DAX.
Calculation Groups -
Yes , but with limits, It can be uses to simulate dynamic measure seclections.
It can operate on measures, not columns.
1.Unpivot your data so that parameters become rows, not columns. This allows Power BI to be flexible and dynamic.
2.If your data is too large, then use a wide table but centralize your parameter logic with calcualtion groups.
3. DAX will be simple and easier to maintain.
Please let me know if you have any questions.If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated — thanks!
Best Regards,
Maruthi
Thanks for your replies.
We did look at calculation groups and couldn't get it to work in this scenario.
But we might not be aware of more advanced techniques with calculation groups, could you maybe elaborate a bit on how you would set them up in this case?
- v-veshwara-msft
Hi styxbe ,
Thanks for the update.
In scenarios where each parameter is in a separate column, Calculation Groups can help reduce repeated logic across measures. Here's how you can set it up:
-
Create a base measure for each parameter column (e.g. [Parameter1 Measure], [Parameter2 Measure], etc.).
-
Create a Calculation Group where each calculation item returns one of those base measures.
-
Use the selected calculation item in your visuals or wrap it in a reusable pattern, depending on how you want the selection to work.
This allows you to centralize the switching logic and avoid repeating SWITCH statements in each measure.
Hope this helps. Please reach out for further assistance.
Please consider marking the helpful reply as Accepted Solution and giving kudos to assist others with similar issues.
Thanks. We looked further into calculation groups but in this case it seems to just make things needlessly more complex and more difficult to maintain. From further research it seems we should try to stick with the unpivot approach anyway, so we'll focus on comparing performance with the switch approach.
-
