Forum Discussion
Advice on optimising SWITCH or alternative approach
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.
Community SupportHI 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 Community
Unpivot 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.