Forum Discussion
Calculation groups to replace slow IF/SWITCH?
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
Hello
Thank you for the reply...
I understand what you are saying but can you please elaborate on the "how" to do what you suggested? Is it by field parameters? another mechanism?
Hi karimm , Thank you for reaching out to the Microsoft Fabric Community Forum.
Yes, the right approach is to use field parameters but instead of exposing them directly as a slicer, you tie them to the existing Period Type slicer (Preset vs Custom). Field parameters differ from IF/SWITCH because only the chosen measure is actually sent to the engine, so you don’t get the slow branch evaluated in the background.
Create a field parameter with both versions of your KPI (e.g. precalculated and custom), then build a simple mapping table that links Preset -> precalculated and Custom -> raw measure. Relate this table to the parameter table so that when users pick a period type, the parameter automatically resolves to the right measure.
The part where you link the period slicer to the parameter table is a design workaround, it may require a bit of model setup and depending on your exact schema you might need to adjust relationships or use a measure to enforce the mapping.
Thank you for your suggestion.
I learnt something new from it about field params but I'm afraid it seems not to improve things.
Below is what I tested and the corresponding results:
I have a landing page with ~15 KPIs and a toggle button triggering one of 2 bookmarks which switch between a preset period of Last 30 days, and an option of custom dates (using slicer of type between).
Each of the KPIs is represented by 2 visuals, one per mode.
Once Preset bookmark is triggered, the 15 visuals using precalculated data are displayed (other 15 are hidden)=>fast.
Once Custom bookmark is triggered, the alternative 15 visuals using raw data are displayed and other 15 are hidden=>slower performance.
Following you recommendation, I created a Parameter table, put in it all 30 measures, and then edited it manually to include 2 additional columns:
1. Value4: 0 or 1 to indicate whether the measure is for preset or custom mode. This column was linked to Dates_Filter table containing dates for each mode + column IsPreset (0/1)
2. Value5: KPI ID. Linked to dim_KPI table.
I then created a duplicate of the landing page, and put only 15 visuals, each of them filtering the relevant KPI.
Using performance analyzer, I tested the time it takes from another 3rd page to the new landing page VS to original page.
To prevent any kind of results external impacts (caching, different load on laptop):
1. all tests were done after saving report on 3rd page, then closing the file and reopening it.
2. Tests were done in sequence with same apps open on the laptop.
RESULTS:
The bookmark-based measures in original page are running at:
- 846-1036ms in Preset
- 1170-1630ms in Custom
The parameter table based measures in new landing page are running at:
- 1347-2145 ms in Preset
- 1155-2694ms in Custom
Conclusions
We can observe that even in PRESET mode, the parameter table based measures are having a worse time than the bookmark based ones. They are actually closer to the CUSTOM mode times of bookmark based measures. Hence it seems that behind the scenes there might be still some eager evaluation occurring.
=> The parameter based solution DOES NOT HELP overcoming the IF/SWITCH performance issue and we still need 2 elements for each KPI
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