Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi there,
I'm looking for some help with a DAX circular dependency error that I can't manage to get around. I've tried using ALLEXCEPT but I've not been able to sort out my problem. Below is a simplified version of the problem I have. I've submitted my M code for query creation as well as the code for the two calculated columns I'm looking to make. One column by itself works fine
M Code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY5LDsAgCAXvwtqkCGq67ecWxvtfo4iWFhMlIZM3vFrhgAAYN3mEFGWJKIMRoYWPktEsg7Kn/NIO04yeq3jXuIeft+hpT81LvdOMXquXtZFjpsVR+c9MOi4qu1dn0qMempT6Jw/ZwSKwPQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Date = _t, Value1 = _t, Value2 = _t]),
#"Sorted Rows" = Table.Buffer(Table.Sort(Source,{{"Date", Order.Ascending}})),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Product"}, {{"Grouped", each _, type table [Product=nullable number, Date=nullable date, Value1=nullable number, Value2=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Indexed", each Table.AddIndexColumn([Grouped],"Index",0,1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Product", "Grouped"}),
#"Expanded {0}" = Table.ExpandTableColumn(#"Removed Columns", "Indexed", {"Product", "Date", "Value1", "Value2", "Index"}, {"Product", "Date", "Value1", "Value2", "Index"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded {0}",{{"Product", type text}, {"Date", type date}, {"Value1", Int64.Type}, {"Value2", Int64.Type}, {"Index", Int64.Type}})
in
#"Changed Type"
Two measures for the summations of the two values
Sum of Value1 = SUM(Data[Value1])
Sum of Value2 = SUM(Data[Value2])
Value1 Change =
VAR FirstFilesIndex = CALCULATE(MIN([Index]), FILTER(ALLEXCEPT('Data','Data'[Product]), Data[Index] + 1 =EARLIER('Data'[Index])))
RETURN
IF(ISBLANK(FirstFilesIndex), 0, [Sum of Value1] - CALCULATE([Sum of Value1],FILTER(ALLEXCEPT('Data',Data[Product]),'Data'[Index] + 1 =EARLIER(Data[Index]))))
Second Calculated Column to do the same for Value2. This is where the circular dependency appears
Value2 Change =
VAR FirstFilesIndex = CALCULATE(MIN([Index]), FILTER(ALLEXCEPT('Data','Data'[Product]), Data[Index] + 1 =EARLIER('Data'[Index])))
RETURN
IF(ISBLANK(FirstFilesIndex), 0, [Sum of Value2] - CALCULATE([Sum of Value2],FILTER(ALLEXCEPT('Data',Data[Product]),'Data'[Index] + 1 =EARLIER(Data[Index]))))
Any ideas how to solve this would be appreciated. If I can find how to submit my .pbix file I'd be more than happy to if it helps
Solved! Go to Solution.
I've never seen measures used in this way before. I don't recommend it.
So I think you should replace the usage of the measures in each column with the appropriate DAX - not too complicated to do.
The DAX is a little long-winded but it looks like it will work.
--------------
Let me know how you get on
"Feeling a bit dim" - None of that please, we all learn on this forum. Funnily enough, I responded to a question 5 minutes after and the person was using measures in a calculated column.
As to the DAX, if I'm understanding it correctly the FirstFilesIndex returns blank if we're on the earliest row for each product (then put 0 in the column) but we don't need to do that, just check for Data[Index] = 0.
I don't really use EARLIER any more either - I prefer variables. Not really a problem though.
The index is probably not required either since you have the date field. Is it a problem performance-wise? Probably not.
Good luck.
I've never seen measures used in this way before. I don't recommend it.
So I think you should replace the usage of the measures in each column with the appropriate DAX - not too complicated to do.
The DAX is a little long-winded but it looks like it will work.
--------------
Let me know how you get on
Thanks HotChili! Feeling a bit dim that it was such a straight forward fix. But yep, like you suggested, removing the measures and replacing the references to them in the calculated columns has fixed things
Out of curiosity, are there any recommendations you could give to simplify the DAX? It seems to work fine but if there are efficiencies that could be made it'd be interesting to hear
Thanks again for the quick response!
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
107 | |
97 | |
39 | |
32 |
User | Count |
---|---|
153 | |
122 | |
77 | |
74 | |
44 |