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'm new to PowerBI. I'm currently calculating using DAX to return the latest data from a table base on its ID, UniqueID and latest date .
At first, everything was fine, I managed to return a value from other table. As I proceed to other column, There's an error message about Circular Dependency. Is there any way to avoid the error? Here's the sample code from my data.
C34 =
VAR LastDayC34 =
CALCULATE (
MAX ( 'TX - Score Table'[Created] ),
NOT ISBLANK ( 'TX - Score Table'[C34] )
)
VAR LastUniqueIDC34 =
CALCULATE(
MAX('TX - Score Table'[ID]),
NOT ISBLANK('TX - Score Table'[C34]))
VAR LastIdC34 =
CALCULATE (
MAX ( 'TX - Score Table'[tag_id]),
'TX - Score Table'[Created] = LastDayC34,
'TX - Score Table'[ID] = LastUniqueIDC34,
NOT ISBLANK ( 'TX - Score Table'[C34] )
)
RETURN
CALCULATE (MAX('TX - Score Table'[C34]), 'TX - Score Table'[tag_id] = LastIdC34,'TX - Score Table'[ID]=LastUniqueIDC34)
C41 = VAR LastDayC41 =
CALCULATE (
MAX ( 'TX - Score Table'[Created] ),
NOT ISBLANK ( 'TX - Score Table'[C41] )
)
VAR LastUniqueIDC41 =
CALCULATE(
MAX('TX - Score Table'[ID]),
NOT ISBLANK('TX - Score Table'[C41]))
VAR LastIdC41 =
CALCULATE (
MAX ( 'TX - Score Table'[tag_id]),
'TX - Score Table'[Created] = LastDayC41,
'TX - Score Table'[ID] = LastUniqueIDC41,
NOT ISBLANK ( 'TX - Score Table'[C41] )
)
RETURN
CALCULATE (MAX('TX - Score Table'[C41]), 'TX - Score Table'[tag_id] = LastIdC41,'TX - Score Table'[ID]=LastUniqueIDC41)
I tried using other formula such as FILTER and MAXX but it was not sucessful.
Thank you for the Tips and Advice 😉
Solved! Go to Solution.
Hi,
The problem you are describing seems to be that you have two calculated columns containing calculate. This will cause issues due to context transition. This post by SQLBI explains this marvelously: "https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/"
The solution to your issue is to use ALLEXCEPT() within your CALCULATE.
I hope this helps to solve your issue and if it does consider accepting this as a solution!
Proud to be a Super User!
Hi @abdmajidnor,
Did ValtteriN 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements.
If that also doesn't help, please share more detailed information to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi,
The problem you are describing seems to be that you have two calculated columns containing calculate. This will cause issues due to context transition. This post by SQLBI explains this marvelously: "https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/"
The solution to your issue is to use ALLEXCEPT() within your CALCULATE.
I hope this helps to solve your issue and if it does consider accepting this as a solution!
Proud to be a Super User!
Here's my sample data, errors on circular dependency and desired value.
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 |
---|---|
42 | |
26 | |
22 | |
11 | |
8 |
User | Count |
---|---|
75 | |
50 | |
47 | |
17 | |
17 |