Optimization of DAX query to get result on each level of aggregation

[Anonymous]

Hi Community,

I have following case. I have base measure:

DiffSales =
CALCULATE ( SUM ( Sales[sale] ), KEEPFILTERS ( Sales[type] = "Total" ) )
    - CALCULATE ( SUM ( Sales[sale] ), KEEPFILTERS ( Sales[type] = "Retail" ) )

Then I want to calculate following one, which give me correct result on each level of aggregation, but running too long and failed with out of memory:

SalesRates :=
SUMX ( Sales, [DiffSales] * [Rate] )

I wanted to make that simpler and rewrite withour base measure. That really works fast. But it works correct only on lowest aggegation level. When I choose any higher aggregation level, this measure just SUM up results from lowest level:

SalesRatesNew :=
VAR DiffSales =
    CALCULATE ( SUM ( Sales[sale] ), KEEPFILTERS ( Sales[type] = "Total" ) )
        - CALCULATE ( SUM ( Sales[sale] ), KEEPFILTERS ( Sales[type] = "Retail" ) )
RETURN
    SUMX ( Sales, DiffSales * [Rate] )

Example of SalesRates (expected) on left side and SalesRatesNew (not expected) on right are below.

Could you please help me, how to rewtire second query to make that works correct and fast as first one.

[marcorusso]

You should review how the context transition works.

The optimal solution is iterating at the Rate cardinality. Assuming the rate is defined by currency and date, you should write something like:

Measure :=
VAR ExchangeGranularity =
    SUMMARIZE ( 
        Sales, 
        ExchangeRate[CurrencyKey],
        'Date'[Date]
    )
VAR Result =
    SUMX ( ExchangeGranularity, [DiffSales] * [Rate] )
RETURN Result

If you want to keep your definition (which is not a good idea IMHO), then:

Measure := 
CALCULATE (
    SUMX ( Sales, Sales[sale] * [Rate] ),
    KEEPFILTERS ( Sales[type] = "Total" )
)
    - CALCULATE (
        SUMX ( Sales, Sales[sale] * [Rate] ),
        KEEPFILTERS ( Sales[type] = "Retail" )
    )

[Anonymous]

HI @marcorusso 

I wanted to simplify my example to post here, but actually it is a little bit tricky.

I have following measures as an input:

DiffSales =
CALCULATE ( SUM ( Sales[sale] ), KEEPFILTERS ( Sales[type] = "Total" ) )
    - CALCULATE ( SUM ( Sales[sale] ), KEEPFILTERS ( Sales[type] = "Retail" ) )

Rate =
IF ([DiffSales] > 0, Rate1, Rate2)

SalesRatesNew :=
SUMX(Sales, [DiffSales] * [Rate] * Sales[Price] * REALTED(Volume[volume]))

And SalesRatesNew works very slow, but correct.

When I replace DiffSales and Rate measure with variables in final measure SalesRatesNew, that works very fast, but doesn't aggregate data on each level.

SalesRatesNew  =
var DiffSales =
CALCULATE ( SUM ( Sales[sale] ), KEEPFILTERS ( Sales[type] = "Total" ) )
    - CALCULATE ( SUM ( Sales[sale] ), KEEPFILTERS ( Sales[type] = "Retail" ) )

var Rate =
IF (DiffSales > 0, Rate1, Rate2)

RETURN
SUMX(Sales, DiffSales * Rate * Sales[Price] * REALTED(Volume[volume]))

So question is what is the difference between using pre defined measures  vs variables in this example.

Thanks

[marcorusso · Accepted solution]

Variables are constants and are evaluated only once - you are making some wrong assumption about them.

However, the solution is still fixing the granularity of your calculation, you are just using the wrong granularity for the context transition of your calculation.

Probably you want to do something like this:

SalesRatesNew :=
SUMX( 
    SUMMARIZE ( Sales, Sales[Price], Volume[volume] ), 
    [DiffSales] * [Rate] * Sales[Price] * Volume[volume]
)

[Anonymous]

Hi @marcorusso 

Thanks for your solution that works much better for me.

Am I correct in how that works?

So, first of all we group Table by columns necessary for calculation. As result we got those columns in output. This happen on filter context, which then transition to row context to iterate over a table and SUMX necessary measures?

---

@marcorusso wrote:

Variables are constants and are evaluated only once - you are making some wrong assumption about them.

However, the solution is still fixing the granularity of your calculation, you are just using the wrong granularity for the context transition of your calculation.

Probably you want to do something like this:

SalesRatesNew :=
SUMX( 
    SUMMARIZE ( Sales, Sales[Price], Volume[volume] ), 
    [DiffSales] * [Rate] * Sales[Price] * Volume[volume]
)

---