Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I am trying use DAX in Power BI to calculate the Max(Difference) column in the table below, and for it to be usable on a line graph with a date slicer (so it can recalculate based upon the date filter applied):
Date | MyVal | MaxMyVal | Difference | MaxDifference |
01/01/2018 | 100 | 100 | 0 | 0 |
02/01/2018 | 101 | 101 | 0 | 0 |
03/01/2018 | 100 | 101 | 1 | 1 |
04/01/2018 | 101 | 101 | 0 | 1 |
05/01/2018 | 102 | 102 | 0 | 1 |
06/01/2018 | 101 | 102 | 1 | 1 |
07/01/2018 | 100 | 102 | 2 | 2 |
So far I have (all formulas below are defined as measures):
Difference := MyTable[MaxMyVal] - SUM(MyTable[MyVal])
However MaxDifference gives me the error "A circular dependency was detected: MyTable[MaxDifference]".
I have tried following the article here - https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/ - however I must not be understanding properly as I can't get this to work while also respecting the date slicer.
Thanks for all your help,
James
Solved! Go to Solution.
Hi @veladon,
MaxDifference = MAXX ( FILTER ( ALLSELECTED ( Mytable[Date] ), MyTable[Date] <= MAX ( MyTable[Date] ) ), [Difference] )
Best regards,
Yuliana Gu
Hi @veladon,
MaxDifference = MAXX ( FILTER ( ALLSELECTED ( Mytable[Date] ), MyTable[Date] <= MAX ( MyTable[Date] ) ), [Difference] )
Best regards,
Yuliana Gu
See if these measures work:
MaxMyVal = VAR __maxDate = MAX([Date]) VAR __table = FILTER(ALL(Table8),[Date]<=__maxDate) RETURN MAXX(__table,[MyVal]) Difference = Table8[MaxMyVal] - SUM(Table8[MyVal]) MaxDifference = VAR __maxDate = MAX([Date]) VAR __table = SUMMARIZE(FILTER(ALL(Table8),[Date]<=__maxDate),[Date],[MyVal],"__MaxMyVal",[MaxMyVal],"__Difference",[Difference]) RETURN MAXX(__table, [Difference])
Attaching PBIX, you want Table8 and Page 3
Hi Greg
Many thanks for the super quick reply. Your answer seems to work, however when increasing the number of rows, the SUMMARIZE function seems to get exponentially slower:
0.5yrs = 1sec
1yr (i.e. 365 rows) = 3s
1.5yrs = 9s
2yrs (730 rows) = 20s
Reading this ( https://www.sqlbi.com/articles/all-the-secrets-of-summarize/ ) I assume this function is using a cross join which is causing this behaviour.
I need this to work for roughly 5 years worth of data, so about 1500 rows. Any other ideas? 🙂
Thanks again,
James
Huh, I actually discussed that when I was live streaming answering that question. Try replacing SUMMARIZE with ADDCOLUMNS
Hi Greg
Using ADDCOLUMNS instead of SUMMARIZE took virtually the same time.
I tried:
and also:
but they both took ~20seconds for 2 years of data rows.
Thanks
James
Thinking about this, I would take out MyMaxValue individually in the summarized table, because that is just adding additional, unnecessary calculations because Difference also calculates that value independently.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |