Calculation groups to replace slow IF/SWITCH?

[karimm]

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 😐

[VahidDM]

@karimm 

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):

1. 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.

2. Put the generated parameter field in your visuals’ Values.

3. 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!! 

LinkedIn|Twitter|Blog |YouTube 

[karimm]

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.