Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Found it myself... The RAW Filter introduces "All" - and with using ALL I introduce the Blank row of the dimension. **bleep**. Obvious...
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...?
Found it myself... The RAW Filter introduces "All" - and with using ALL I introduce the Blank row of the dimension. **bleep**. Obvious...
Hi @ppvinsights
please share example / file of data as input for expected output.
Proud to be a Super User!
User | Count |
---|---|
51 | |
27 | |
19 | |
18 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
23 | |
22 |