Default item calculation groups

[burakg]

Hi all,

I've created a calculation group for time intelligence calculation.

Due to the setup of our tooling, I need DAX to default to a default item if you don't select anything.

Let's say I have a measure which just sums the colum Sales, called [Total sales].

I created calculation group SelectTime which calculate it for 2 items, and I use a disconnected period table:

- Current period = 

 CALCULATE ( SELECTEDMEASURE(), DATESBETWEEN ( DimDate[idDate], FIRSTDATE ( Period[PeriodBeg] ), LASTDATE ( Period[PeriodEnd] ) ) )

- Prior period (=current period - 1 year)

 CALCULATE ( SELECTEDMEASURE(), DATESBETWEEN ( DimDate[idDate], FIRSTDATE ( Period[PriorPeriodBeg] ), LASTDATE ( Period[PriorPeriodEnd] ) ) )

So I need the model to default to Current period if I don't select anything for the calculation group.

My current solution is:

VAR SelectedTime = IF ( HASONEVALUE ( 'SelectTime'[Time] ), SELECTEDVALUE ( 'SelectTime'[Time] ), "Current period" ) 
RETURN 
CALCULATE ( [Total sales], 'SelectTime'[Time] = SelectedTime ) )

This solution works, as it correctly uses Current period when nothing is selected, and returns prior periods when I select that.

However, it feels like I'm paying a performance penalty by introducing an unnecessary VAR and an extra calculate.

Is there any other solution that avoids using this construction, while still being able to select Current period as the default item?

A different solution I tried is baking in the current period selection into the Total sales measure, hopin that the calculation group would then override this default date filtering, but then the calculation groups all just show Current period, no matter if I select Prior period.

[datalus]

Calculation groups have been extended to support selection expressions which target your use case. You can define a 'noSelectionExpression' which is evaluated for any measure, whenever the parent calculation group is not-filtered. You can copy the 'Current period' calc item's expression in to the noSelectionExpression and this will be applied for any measure, not just [Total Sales]. If you want to avoid applying this expression to every measure, you can make an allow list by doing something like: SWITCH(SELECTEDMEASURENAME(), "Total Sales", ...., SELECTEDMEASURE()).

[Anonymous]

Hi @burakg ,

Regarding your question, is your data volume very large? I think your initial solution is more intuitive and easier to implement.

Best Regards,
Wenbin Zhou

[bgc200]

It depends, usually a project takes about 100-200k rows, but occasionally it also goes into the multiple millions of rows for the dataset.

Is there a known performance impact of using variables this way?

I did do a performance test of the old switch solution vs the new calculation group methodology with the variable, and it looks like the performance of refreshing reports is almost similar.

[Anonymous]

Hi @bgc200 ,

I don't have experience with very large data sets. But when you use a variable, it will only evaluate the expression once and store the result in the corresponding variable. This is good for performance.

Best Regards,
Wenbin Zhou