Forum Discussion

ppvinsights's avatar
ppvinsights
Helper III
2 years ago
Solved

Measure Caching in tables and circular dependency

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's avatar
    ppvinsights
    2 years ago

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

     

     

3 Replies

  • 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's avatar
      ppvinsights
      Helper III

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