Measure Caching in tables and circular dependency

[ppvinsights]

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

[ppvinsights]

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

[ppvinsights · Accepted solution]

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

[some_bih]

Hi @ppvinsights 

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