Forum Discussion
Calculation groups to replace slow IF/SWITCH?
Hi All
I would appreciate your help to understand whether this idea suggested by genAI is true or a hallucination.
I have a report in which the main page has many KPIs and 2 modes related to period selection, preset and custom.
Preset is for predefined periods (Last 7/14/30 Days).
Custom allows the user to select any date range.
The switch between the 2 modes is done via bookmarks.
Each KPI is represented by 2 measures, one for preset dates and another for the custom.
The bookmarks are basically showing preset version of the measures and hiding the custom version or vice versa.
The preset mode KPI measures use precalculated data, which makes them fast.
The custom mode KPI measures use non-precalculated data which makes them slower than the preset ones.
Example of measures:
1. [Builds_Triggered_preset] is used in custom mode and uses raw data.
2. [Builds_Triggered_precalculated] is used in preset mode and uses precalculated aggregated data.
In the beginning, we tried to avoid bookmarks by creating parent measure that would contain an IF/SWITCH function that would decide whether it should use precalculated data or not.
Example:
[Builds_Triggered]= IF(SELECTEVALUE(dimDate[Period]="Custom",[Builds_Triggered_precalculated],[Builds_Triggered_preset])
When we tried this, we faced performance issues where the slower time was always recorded even when the fast precalculation logic condition was fulfilled . We suspect the IF/SWITCH function was doing eager evaluation. A ticket to the Microsoft support team didn't help either...
So the bookmarks are there now to show/hide the relevant version of the measures.
But these bookmarks are causing an overhead in the development as they require additional maintenance and testing all the time.
MS Copilot suggested that calculation groups instead can be used instead of IF/SWITCH thus avoid the eager evaluation.
Is it really possible in this scenario? and how to do that?
I had a very long conversation with Copilot and I feel I'm going in circles đ
Hi karimm , Thank you for reaching out to the Microsoft Fabric Community Forum.
Youâre right, even when Preset is selected, Power BIâs query engine still ends up touching the expressions behind the slow measure, so you donât get the fast-only path. Field parameters and calculation groups change how you organize/switch measures, not how the DAX engine chooses what to evaluate, so they wonât reliably prevent the other branch from being computed.
The reason your bookmark setup performs best is simple: bookmarks physically hide the visuals that reference the heavy measures. When visuals arenât rendered, Power BI doesnât query their measures at all, meaning only the fast queries run. Thatâs why youâre seeing consistent, predictable performance there.
If you want to remove bookmark maintenance while keeping the same speed, the only real fix is at the model level. Build a pre-aggregated table for your preset date ranges and configure Manage Aggregations (or a composite model). That way, Power BI automatically routes preset queries to the aggregated table and only uses raw data for true custom ranges, no branching logic or bookmarks needed.
Create calculation groups in Power BI - Power BI | Microsoft Learn
Calculation groups in Analysis Services tabular models | Microsoft Learn
User-defined aggregations - Power BI | Microsoft Learn
Use report readers to change visuals - Power BI | Microsoft Learn
10 Replies
Short answer: calculation groups wonât magically stop the âotherâ branch from being computed. DAX can evaluate both sides of IF/SWITCH depending on dependencies, so you can still pay the slow cost. Using a calc group doesnât change that.
Use a Measure Field Parameter (not bookmarks, not IF/SWITCH):
-
Create a measure parameter with two entries per KPI:
⢠[Builds_Triggered_precalculated]
⢠[Builds_Triggered_custom]
Power BI will generate a parameter table and a slicer.
-
Put the generated parameter field in your visualsâ Values.
-
Use the parameter slicer (or sync it across pages) to choose Preset vs Custom.
Why this works: the query only includes the selected measure, so the other measure is not evaluated at all â you get the fast path when âPresetâ is chosen, with no bookmark maintenance.
Tip: Do the same for all KPIs (each parameter can include pairs of measures), or create one parameter per KPI group if you need different slicers.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Thank you for your reply.
Maybe I'm missing something... If I understsood it correctly, you are suggesting to use slicers to choose the relevant measure to be used.
But what I need is actually to decide which measure to use based on the period type selected by the user.
So when a user selects any preset period, the precalculated measure will be used. And when "Custom" period is used in the period slicer, the non-precalculated measure will be used.
Hi karimm , Thank you for reaching out to the Microsoft Fabric Community Forum.
Calculation groups wonât change how DAX evaluates branches. Theyâre powerful for measure formatting and reuse but they donât eliminate the eager evaluation you saw with IF/SWITCH. So, Copilotâs suggestion is misleading in this specific context.
What you actually need is a way for the period slicer itself to control which version of each KPI gets passed into visuals. Instead of bookmarks or inline IFs, you can model this as a mapping between the period type and the right measure. The slicer drives that mapping and only the mapped measure is ever executed.
This works because the measure isnât conditionally branching inside DAX, the engine only sees the selected measure reference. That gives you the fast path when a preset is chosen and the flexible path when custom dates are used, with no maintenance overhead from bookmarks.
-
Hi karimm , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.
Hi karimm , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know â your feedback can really help others in the same situation.
Hi karimm,
Just wanted to follow up and confirm that everything has been going well on this. Please let me know if thereâs anything from our end.
Please feel free to reach out Microsoft fabric community forum.Thank you.
