Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results forย 
Search instead forย 
Did you mean:ย 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

View discussion in a popup

Replying to:
lostpowerbidev
New Member

Need help with Circular Dependency Workaround

โ€Ž09-11-2025 08:31 AM

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 DaysWithin 7 days?Consolidated WeightDays between AGICumulative DaysTrue Consolidation?EXPECTED OUTCOME
US13-05042420548/13/2024Different Lane     
US13-05042420548/14/2024Yes                          407.0911YesYes
US13-05042420548/16/2024Yes                             26.3323YesYes
US13-05042420548/20/2024Yes                          560.4747YesYes
US13-05042420548/26/2024Yes                          840.04613ResetReset
US13-05042420549/4/2024More than 7 days      
US13-05042420549/10/2024Yes                      1,006.4366YesYes
US13-05042420549/17/2024Yes                          587.96713ResetReset
US13-05042420549/18/2024Yes                          118.51114ResetYes
US13-05042420549/25/2024Yes                          243.06721ResetReset
US13-050424205410/1/2024Yes                          340.85627ResetYes
US13-050424205410/4/2024Yes                          519.15330ResetReset
US13-050424205410/14/2024More than 7 days      
US13-050424205410/23/2024More than 7 days      
US13-050424205410/24/2024Yes                          619.8611YesYes
US13-050424205411/13/2024More than 7 days      
US13-050424205411/20/2024Yes                          723.6077YesYes
US13-050424205411/25/2024Yes                          644.76512ResetReset
US13-050424205412/3/2024More than 7 days      
US13-050424205412/9/2024Yes                          397.6766YesYes
US13-050424205412/16/2024Yes                          348.29713ResetReset
US13-050424205412/19/2024Yes                          399.47316ResetYes
US13-050424205412/19/2024Yes                          121.56016ResetReset
US13-050424205412/26/2024Yes                          517.77723ResetYes

 

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!

519 Views