0

I have created two calculation groups in an azure analysis ( B0 ) environment. The first one is a list of measures that represent a score card. Most lines use the SELECTEDMEASURE(), but a few of them have a hard measure selected. The other calculation group has some Time intelligence (YTD, Prior Year & variances in % ) as well as a split in Actual and Budget. This is what the result looks like if i put a simple Amount measure ( SUM('fact'[Amount]) ) in.

Result of Visual

The logic in the measures is not ground breaking, these are the elements of the time structure:

Period Actuals: CALCULATE(SELECTEDMEASURE();'Fact'[Version]="Actuals")

Period Budget: CALCULATE(SELECTEDMEASURE();'Fact'[Version]="Budget O")

Period Variance: CALCULATE(SELECTEDMEASURE();'Structure'[Structure]="Period Actuals") -CALCULATE(SELECTEDMEASURE();'Structure'[Structure]="Period Budget")

Period Variance % :Divide( CALCULATE(SELECTEDMEASURE();'Structure'[Structure]="Period Variance") ; CALCULATE(SELECTEDMEASURE();'Structure'[Structure]="Period Budget") ;0)

YTD Actuals: CALCULATE(SELECTEDMEASURE();DATESYTD('Calender'[Date];"12-31");'Structure'[Structure] = "Period Actuals")

YTD Budget: CALCULATE(SELECTEDMEASURE();DATESYTD('Calender'[Date];"12-31");'Structure'[Structure] = "Period Budget")

YTD Current Variance: CALCULATE(SELECTEDMEASURE();'Structure'[Structure]="YTD Actuals") - CALCULATE(SELECTEDMEASURE();'Structure'[Structure]="YTD Previous Year")

YTD Current Year: CALCULATE(SELECTEDMEASURE();'Structure'[Structure] = "YTD Actuals")

YTD Previous Year: CALCULATE(SELECTEDMEASURE();DATESYTD(SAMEPERIODLASTYEAR('Calender'[Date]);"12-31");'Structure'[Structure] = "Period Actuals")

YTD Variance: CALCULATE(SELECTEDMEASURE();'Structure'[Structure]="YTD Actuals") - CALCULATE(SELECTEDMEASURE();'Structure'[Structure]="YTD Budget")

YTD Variance %: Divide( CALCULATE(SELECTEDMEASURE();'Structure'[Structure]="YTD Variance"); CALCULATE(SELECTEDMEASURE();'Structure'[Structure]="YTD Budget") ;0)

The measure in the P&L structure are alse quite simple, here are some examples:

Gross Margin WG : CALCULATE ( SELECTEDMEASURE(); GLAccount[Level03 code] = "PL00_00")*-1

Gross Margin WG - Cranes : CALCULATE ( SELECTEDMEASURE(); GLAccount[Level03 code] = "PL00_00";'Fact'[MachineType] = "LBCC")*-1

Quantity Cranes: CALCULATE ( sum('Fact'[Qty]); GLAccount[Level04 code] = "PL00_00_000";'Fact'[MachineType] = "LBCC")

The model is quite small, SQL Management studio estimates its size to about 250MB.

The issue is that when we are using one of the names calculation groups we get normal responds times ( 2 seconds at most ) but when we use both it can take up to several minutes to get a result in Power BI. Excel is faster, but still takes up to 30 seconds. Meanwhile the memory consumption in AS goes up by several GB ?!?

This becomes worse when navigating ( filtering or adding dimensions ) up to the point where PowerBI just stops working.

We have tried changing the DAX formula's but we can figure out why it is using so much memory. Hope one of you can send us in the right direction.

Thanks!

Daniel Sanders
  • 67
  • 1
  • 6
  • I suspect it's a case of the engine writing overly complex and inefficient query plans. I've had [similar issues](https://stackoverflow.com/questions/61805513) and I don't think there are easy fixes that work generally. – Alexis Olson Jun 11 '20 at 18:18
  • It might be caused by the wrong data structure or relations, i.e. maybe you are creating a Cartesian product somewhere, or filtering by tall fact tables. Hard to say without knowing your data model. – RADO Jun 11 '20 at 19:16
  • Maybe the problem is concerned 'FormatStringExpression' if specified? It is worth checking how it works without. – user14202733 Sep 01 '20 at 13:02

0 Answers0