Circular Dependency preventing second calculated column creation

[johnny19]

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])

First Calculated Column to find the difference between consecutive reports for each Product's Value1 number

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

[HotChilli]

"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.

[HotChilli · Accepted 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

[johnny19]

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!