Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
johnny19
Frequent Visitor

Circular Dependency preventing second calculated column creation

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

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

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

View solution in original post

3 REPLIES 3
HotChilli
Super User
Super User

"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
Super User
Super User

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!

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.