Need help with Circular Dependency Workaround

[lostpowerbidev]

Hi all,

I'm working on a shipment consolidation model in Power BI and need help building a calculated column that tracks consolidation logic after a reset. I have been banging my head on the wall since early yesterday and can not figure out a workaround that works as expected or doesn't result in a circular depending.

Context:

I have a table called Delivery Data YH with the following columns:

• CustConcatKey – customer grouping
• Date – shipment date
• Index – sorted row index
• DaysBtwAGI – days between shipments
• True Consolidation – either "Yes", "Reset", or blank
• CombinedWeightCol – total weight of consolidated shipments

My data is already sorted/indexed and I am using row dependency for a large amount of my columns. 

What I’m trying to do:

I want to analyze a large set of data (hence using PowerBI) and am create columns to mimic what has been used by another coworker within excel who was looking at a much smaller data set. I want to create a column called ConsolidationAfterReset that:

1. Is blank if True Consolidation is blank.
2. Is "Reset" if True Consolidation is "Reset".
3. For rows after the most recent reset, checks:
   • If CombinedWeightCol ≤ 18143.7 → mark "Yes"
   • Else → mark "Too Heavy"

The goal is to evaluate consolidation viability after each reset.

Here is the excel I am trying to mimic: 

I have all columns working properly besides the last one. I am using the highlighted row as an example and no matter what I try, I can not get it to work as expected. 

Here is what I have in PBI for now --> mostly matches: 

Concat Key(Cust)	AGI Days	Within 7 days?	Consolidated Weight	Days between AGI	Cumulative Days	True Consolidation?	EXPECTED OUTCOME
US13-0504242054	8/13/2024	Different Lane
US13-0504242054	8/14/2024	Yes	407.09	1	1	Yes	Yes
US13-0504242054	8/16/2024	Yes	26.33	2	3	Yes	Yes
US13-0504242054	8/20/2024	Yes	560.47	4	7	Yes	Yes
US13-0504242054	8/26/2024	Yes	840.04	6	13	Reset	Reset
US13-0504242054	9/4/2024	More than 7 days
US13-0504242054	9/10/2024	Yes	1,006.43	6	6	Yes	Yes
US13-0504242054	9/17/2024	Yes	587.96	7	13	Reset	Reset
US13-0504242054	9/18/2024	Yes	118.51	1	14	Reset	Yes
US13-0504242054	9/25/2024	Yes	243.06	7	21	Reset	Reset
US13-0504242054	10/1/2024	Yes	340.85	6	27	Reset	Yes
US13-0504242054	10/4/2024	Yes	519.15	3	30	Reset	Reset
US13-0504242054	10/14/2024	More than 7 days
US13-0504242054	10/23/2024	More than 7 days
US13-0504242054	10/24/2024	Yes	619.86	1	1	Yes	Yes
US13-0504242054	11/13/2024	More than 7 days
US13-0504242054	11/20/2024	Yes	723.60	7	7	Yes	Yes
US13-0504242054	11/25/2024	Yes	644.76	5	12	Reset	Reset
US13-0504242054	12/3/2024	More than 7 days
US13-0504242054	12/9/2024	Yes	397.67	6	6	Yes	Yes
US13-0504242054	12/16/2024	Yes	348.29	7	13	Reset	Reset
US13-0504242054	12/19/2024	Yes	399.47	3	16	Reset	Yes
US13-0504242054	12/19/2024	Yes	121.56	0	16	Reset	Reset
US13-0504242054	12/26/2024	Yes	517.77	7	23	Reset	Yes

The excel eqn looks like this (with columns replaced with my query columns) -->  =IF(CumulativeDays="","",IF(S292="Reset",IF(CombinedWeight<40000,"Yes","Too Heavy"),IF(CumulativeDays>7,"Reset",IF(CombinedWeight<40000,"Yes","Too Heavy")))) where S292 is the circular dependency that is looking at the Consol.AfterReset col. in excel. 

How can I correctly implement this logic in a calculated column so that it evaluates rows after each reset, and applies the weight threshold condition only to those?

Any help or DAX suggestions would be greatly appreciated!

[v-karpurapud]

Hi @lostpowerbidev 

Just checking in as we haven't received a response to our previous message. Were you able to review the information above? Let us know if you have any additional questions.

Thank You.

[v-karpurapud]

Hi @lostpowerbidev 

I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.

Thank You.

[v-karpurapud]

Hi @lostpowerbidev 

Thank you for submitting your question to the Microsoft Fabric Community Forum.

As @samratpbi  suggested, move that first step of logic (cumulative days, reset identification, and related calculations) into Power Query instead of building it entirely with calculated columns. By doing this, the table that loads into your model already contains the reset and cumulative information, which removes the circular dependency issue and makes the DAX model simpler and more efficient.

I hope this information is helpful. . If you have any further questions, please let us know. we can assist you further.

Regards,

Microsoft Fabric Community Support Team.

 

[FBergamaschi]

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Alternatively, you can share your .pbix via some cloud service and paste the link here. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here

[lostpowerbidev]

Hello @FBergamaschi, I have included a table with a small sample of my data as well as expected results. The first 2 columns are static data. Column 3 is calculated based on the date diff between the current and previous row. Columns 5 - 7 are calculated as well and the final column includes my expected result. Thanks again and let me know if there is anything else I can do to help specify.

[samratpbi · Accepted solution]

Hi, not very sure about the data what you have in the excel and in the main table, however looking into the details it seems you are first deriving the 1st screenshot in dax and then trying to do the 2nd one. You can do one thing, 1st step try to achieve using power query. In that way, there will not be any circular dependency.

If this helps to resolve your problem, then please mark it as solution, thanks