Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Making calculated columns my first one is this :
IF('AVN Rework Merge'[Flow] = "Reworked" && [Amt USD] > -50,[Quantity],0)
It works fine and gives me the quantity that I expected.
Now I want a similar one that gives met the USD amount instead of the Quantity so I made :
IF('AVN Rework Merge'[Flow] = "Reworked" && [Amt USD] > -50,[Amt USD],0)
But then suddenly it says I have a circular dependancy and I cannot understand why it is doing this.
Solved! Go to Solution.
@rpinxt Instead of referencing the measure [FinAmtUSD], try referencing the column directly instead.
so:
@rpinxt I agree with @m_alireza. Also, any specific reason why you went with Calculated Columns instead measures?
I rewrote the logic as a measure for you. Check if this meets your requirement
Reworked Qty <50__ =
VAR Result = CALCULATE(
[Quantity],
FILTER('AVN Rework Merge', 'AVN Rework Merge'[Flow] = "Reworked" && [Amt USD] > -50)
)
RETURN
COALESCE(Result,0)
@rpinxt Instead of referencing the measure [FinAmtUSD], try referencing the column directly instead.
so:
@rpinxt I agree with @m_alireza. Also, any specific reason why you went with Calculated Columns instead measures?
I rewrote the logic as a measure for you. Check if this meets your requirement
Reworked Qty <50__ =
VAR Result = CALCULATE(
[Quantity],
FILTER('AVN Rework Merge', 'AVN Rework Merge'[Flow] = "Reworked" && [Amt USD] > -50)
)
RETURN
COALESCE(Result,0)
@SivaMani @m_alireza I want to thank you both because both solutions seem to work! 😁
Well I chose for calculated column because I merged two sources and thought to get the correct amount on every line a calculated column would be better.
Also I tried a measure before but could not succeed 😑
Must say I do not fully understand your measure.
What does COALESCE do?
And what would you guys think is best in ways of performance?
Would the measure be better (lighter) on the data then the calculated columns?
@rpinxt, You're welcome!
COALESCE - If the first expression is BLANK, it returns zero. It is nothing but IF(ISBLANK(Result),0, Result )
Calculated Columns vs Measures -Both have their own use cases.
Refer the below URLs for more info - https://community.powerbi.com/t5/Desktop/column-vs-measure/m-p/13203/highlight/true#M3305
Thanks!
Well yes both logics here are Calculated Columns:
Calculated Column 1:
FirstColumn = IF('AVN Rework Merge'[Flow] = "Reworked" && [Amt USD] > -50,[Quantity],0)
Calculated Column 2:
SecondColumn = IF('AVN Rework Merge'[Flow] = "Reworked" && [Amt USD] > -50,[Amt USD],0)
And yes they are looking at the same columns but column1 returns the Quantity and column2 should return the USD Amount.
But as I can see they are not referencing eachother....
@rpinxt, I couldn't reproduce the issue based on the info you provided. Can you share your pbix file with sample data (remove any sensitive data), if possible?
Could try to do that, but how can one add the pbix file here?
Upload it to OneDrive or Google Drive and share the URL
Ok...wel lets see if this works:
https://drive.google.com/file/d/1vFjEX1zQw4xEA2R6gnCWQp1jvyPau54B/view?usp=sharing
@rpinxt, What about the 'AVN Rework Merge'[FinAmtUSD] and 'AVN Rework Merge'[Flow]? Are they direct columns from the source or calculated in Power BI?
FinAmtUSD is directly from the source.
Flow is also directly from the source be it that it comes from an excel file.
The excel file was merged with the source based on a similar field in both the source and the excel file (FlowKey).
So in base the 2 fields are not calculated.
The Amt USD measure refers to a field from the table like :
@rpinxt, As you see in the error message, there is another column 'AVN Rework Merge'[Column] - It might be referencing the same columns in its expression. Circular Dependency is detected whenever two columns referenced each other. Check your calculated columns logic.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |