topic Re: Measure Caching in tables and circular dependency in DAX Commands and Tips

Measure Caching in tables and circular dependency

ppvinsights — Thu, 09 Nov 2023 09:33:45 GMT

Hi community,

in a current report I have performance problems - I always receive a timeout in the service. Because I cannot find a better way to calculate my measures, I thought about introducing a cache table: I take my dimensions in a CROSSJOIN statement, surround it by ADDCOLUMNS and let my measure/s be caculated for every row in a new "cache table".

I would love to still work with my original dimensions. But I cannot connect my dimension-columns of my "cache table" with any dimension - because I would introduce a circular dependency. Is there any best practise to solve that?

Thanks

Holger

Re: Measure Caching in tables and circular dependency

some_bih — Thu, 09 Nov 2023 14:18:31 GMT

Hi ppvinsights

please share example / file of data as input for expected output.

Re: Measure Caching in tables and circular dependency

ppvinsights — Thu, 09 Nov 2023 16:07:00 GMT

I just tried to build an easy example with Testdata. And, yes, sure: everything worked. So it took me a while to find a difference:

I have a Table called "Person". I have another table called "Order". And I have a calendar table. I have a measure "OrderValue=sum(Order[TotalDue])" - nothing special.

Now I build my "cache table" - I want to precalculate "total due per person per year):

Cache table = ADDCOLUMNS( CROSSJOIN( DISTINCT(DimKalender[Jahr]), DimPerson ), "value", [OrderValue] )

When I try to create a relation 'Cache Table'[BusinessEntityID] - 'DimPerson'[BusinessEntityID] everything works fine.

Now I change the DAX-statement a little bit - because I only want to see customers called "Yang":

Cache table = var c = CALCULATETABLE(DimPerson, FILTER(DimPerson,DimPerson[LastName] = "Yang")) return ADDCOLUMNS( CROSSJOIN( DISTINCT(DimKalender[Jahr]), c ), "value", [OrderValue] )

==> Everything still works fine.

So I found another difference between my implementation and my example: I used a RAW Filter:

Cache table = var c = CALCULATETABLE(DimPerson, DimPerson[LastName] = "Yang") return ADDCOLUMNS( CROSSJOIN( DISTINCT(DimKalender[Jahr]), c ), "value", [OrderValue] )

And now I have a circular dependency between my cache table and DimPerson.

Can anyone explain that for me...?

Re: Measure Caching in tables and circular dependency

ppvinsights — Thu, 09 Nov 2023 18:41:56 GMT

Found it myself... The RAW Filter introduces "All" - and with using ALL I introduce the Blank row of the dimension. **bleep**. Obvious...