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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
alexeisenhart
Resolver I
Resolver I

Circular dependency with calculated column - can't find the source

I'm looking at pallet movement within a warehouse.

 

I have a calculated column on my ShippedPallets_10Days dataset, Waited on LP, that determines if pallets were produced by a robot after the pallet's order was released (aka, determines if the palletizer is behind in its work and delaying shipments). Today I created a new calculated column, to determine if the case pickers were behind, by copy/pasting the formula for Waited on LP and adjusting the location comparison.

 

Both columns are being added to ShippedPallet_10Days. The DAX for this second query is the following (and it is identical to Waited on LP's DAX except for the final string comparison).

 

Waited on Case Pick = 

var firstMovementTimestamp = calculate(
    MIN ( PalletMovement_10Days[Timestamp])
    , FILTER(
        PalletMovement_10Days
        , PalletMovement_10Days[iContainerID] = ShippedPallet_10Days[iContainerID]
    )
    , ALLEXCEPT(ShippedPallet_10Days, ShippedPallet_10Days[Waited on LP])
)

return if
(
    ShippedPallet_10Days[Load Released Datetime] <> BLANK()
    && firstMovementTimestamp >= ShippedPallet_10Days[Load Released Datetime] 
    && LEFT 
    (
        CALCULATE
        (
            FIRSTNONBLANK(PalletMovement_10Days[From], 0),
            FIRSTNONBLANK(PalletMovement_10Days[Timestamp], 0),
            FILTER(
                ALL(PalletMovement_10Days),
                PalletMovement_10Days[iContainerID] = earlier(ShippedPallet_10Days[iContainerID])
            )
            , ALLEXCEPT(ShippedPallet_10Days, ShippedPallet_10Days[Waited on LP])
        )
    	, 2
    ) = "MW", "Case Pick Delayed", "No Case Pick Delay"
)

Naturally, this produced a circular reference and I've been struggling with this all day. I've read the article that everyone suggests (https://www.sqlbi.com/articles/understanding-circular-dependencies/) -- that gave me some ideas but I still can't resolve the issue.

 

I tried:

  • Adding the ALLEXCEPT filters to remove the dependency on the Waited on LP.
  • Adding index columns to ShippedPallets, ShippedOrders and ShippedLoads (chosen arbiraritly).
  • Reviewing the dependencies via DAX Studio -- there are dependencies to all sorts of fields from all over my report, included a bunch of auto-generated date dimension tables (even though I have my own date dimension dataset).

Can anyone offer any advice? I'm just spinning my wheels now.

 

Or, any ideas on how to solve the problem with a different DAX query? I'm trying to detect if the first movement of the shipped pallet is = "XXX" or "YYY" when the first movement occurs after the associated load was released. That seems like it should be an easier question to solve than I might be making it.

 

1 ACCEPTED SOLUTION

After sinking more time than I'd like to admit, I still have no idea specifcially why there was a cicular reference, nor how to fix the original problem. But I skirted around the issue by changing how I'm calculating the pallet's first location and then moving that part of the shared formula to a separate calculated column.

 

First Location = 
    VAR FirstLocationDatetime =
        CALCULATE(
            FIRSTNONBLANKPalletMovement_10Days[Timestamp], 0 )
            , FILTER (
                ALL(PalletMovement_10Days )
                , PalletMovement_10Days[iContainerID] = earlier(ShippedPallet_10Days[iContainerID])
            )
        )
    RETURN
        CALCULATE (
            FIRSTNONBLANK(PalletMovement_10Days[From], 1 )
            , FILTER (
                ALL(PalletMovement_10Days ),
                PalletMovement_10Days[Timestamp] = FirstLocationDatetime
                && PalletMovement_10Days[iContainerID] = ShippedPallet_10Days[iContainerID]
            )
        )

Waited on Case Pick = 
    var firstMovementDatetime = calculate(
        MIN(PalletMovement_10Days[Timestamp])
        , FILTER (PalletMovement_10Days, PalletMovement_10Days[iContainerID] = ShippedPallet_10Days[iContainerID]
        )
        , ALLNOBLANKROW(ShippedPallet_10Days[iContainerID])
        , ALLNOBLANKROW(PalletMovement_10Days[iContainerID], PalletMovement_10Days[Timestamp])
    )
    return if(
        ShippedPallet_10Days[Load Released Datetime] <> BLANK()
        && firstMovementDatetime >= ShippedPallet_10Days[Load Released Datetime] 
        && LEFT (ShippedPallet_10Days[First Location] , 2) = "MW", "Case Pick Delayed", "No Case Pick Delay")

Waited on LP = 
    var firstMovement = calculate
    (
        MIN(PalletMovement_10Days[Timestamp])
        , FILTER(PalletMovement_10Days, PalletMovement_10Days[iContainerID] = ShippedPallet_10Days[iContainerID])
        , ALLNOBLANKROW(ShippedPallet_10Days[iContainerID])
        , ALLNOBLANKROW(PalletMovement_10Days[iContainerID], PalletMovement_10Days[Timestamp])
    )
    return if(
        ShippedPallet_10Days[Load Released Datetime] <> BLANK()
        && firstMovement >= RELATED(ShippedLoad_10Days[First Order Released Datetime])
        && ShippedPallet_10Days[First Location] = "Queue 266", "Waited on LP", "Did not wait"
    )

View solution in original post

7 REPLIES 7
alexeisenhart
Resolver I
Resolver I

I compared the dependencies for Waited on LP before and after making the following changes to all calculated columns on ShippedPallet:

  • Changed all ALL to ALLNOBLANKROW
  • Handle all implicit ALL in CALCULATETABLE by expliclty using ALLNOBLANKROW
  • AddedALLEXCEPT(ShippedPallet_10Days, ShippedPallet_10Days[Waited on LP]) to all CALCULATE
  • Changed all VALUES to DISTINCT

I compared the dependencies before and after thes changes but the dependency list didn't change at all.

 

I'm getting the dependency list by querying in DAX Studio with --

 

SELECT *
From $SYSTEM.DISCOVER_CALC_DEPENDENCY 
WHERE 
( OBJECT_TYPE = 'MEASURE' OR OBJECT_TYPE = 'CALC_COLUMN')
and [table] = 'ShippedPallet_10Days'
and [object] = 'Waited on LP'

For your VAR and IF calcs I think you can specify ALL(ShippedPallet_10Days, ShippedPallet_10Days) as the exclicit comparision your making should provide the necessary filter context.

 

Note to make it easier to read and know if I'm using a column from that table in conext to only that row vs a TABLE refence I use the column name in the [] without the table reference.

 

 

Hi @Seward12533,

 

Thanks for your message! I don't think I understand. 😃

 

I'm not sure where you mean for me to use ALL in the VAR and IF calculations. I tried replacing the FILTERs where I thought it was  appropriate but the ALL function reports that "multiple table arguments are not allowed in ALL/ALLNOBLANKROW function."

 

For your second point, I assumed that saying ShippedPallet[ColumnName] is the same as saying [ColumnName], assuming that [ColumnName] can be determined to be coming from the ShippedPallet dataset (i.e., no other in-scope datasets are using that column name also). Is this not the case, or am I misunderstanding?

 

Thank you again!

Yes on the 2nd Point. If you writing in a calculated column [column name] without table prefix will refer to the value from column as its the only one in scope.

 

 For the first point  like this.

 

Instead of this

 , ALLEXCEPT(ShippedPallet_10Days, ShippedPallet_10Days[Waited on LP])

use this

 

 , ALL(ShippedPallet_10Days), ALL(ShippedPallet_10Days)

 

That's an interesting idea. I don't understand why that would fix it, but I tried replacing the two ALLEXCEPT commands in each of these two columns with ALL(ShippedPallet_10Days), ALL(ShippedPallet_10Days), but no apparent effect: still have the circular reference and the reference count for the entire file has not changed.

After sinking more time than I'd like to admit, I still have no idea specifcially why there was a cicular reference, nor how to fix the original problem. But I skirted around the issue by changing how I'm calculating the pallet's first location and then moving that part of the shared formula to a separate calculated column.

 

First Location = 
    VAR FirstLocationDatetime =
        CALCULATE(
            FIRSTNONBLANKPalletMovement_10Days[Timestamp], 0 )
            , FILTER (
                ALL(PalletMovement_10Days )
                , PalletMovement_10Days[iContainerID] = earlier(ShippedPallet_10Days[iContainerID])
            )
        )
    RETURN
        CALCULATE (
            FIRSTNONBLANK(PalletMovement_10Days[From], 1 )
            , FILTER (
                ALL(PalletMovement_10Days ),
                PalletMovement_10Days[Timestamp] = FirstLocationDatetime
                && PalletMovement_10Days[iContainerID] = ShippedPallet_10Days[iContainerID]
            )
        )

Waited on Case Pick = 
    var firstMovementDatetime = calculate(
        MIN(PalletMovement_10Days[Timestamp])
        , FILTER (PalletMovement_10Days, PalletMovement_10Days[iContainerID] = ShippedPallet_10Days[iContainerID]
        )
        , ALLNOBLANKROW(ShippedPallet_10Days[iContainerID])
        , ALLNOBLANKROW(PalletMovement_10Days[iContainerID], PalletMovement_10Days[Timestamp])
    )
    return if(
        ShippedPallet_10Days[Load Released Datetime] <> BLANK()
        && firstMovementDatetime >= ShippedPallet_10Days[Load Released Datetime] 
        && LEFT (ShippedPallet_10Days[First Location] , 2) = "MW", "Case Pick Delayed", "No Case Pick Delay")

Waited on LP = 
    var firstMovement = calculate
    (
        MIN(PalletMovement_10Days[Timestamp])
        , FILTER(PalletMovement_10Days, PalletMovement_10Days[iContainerID] = ShippedPallet_10Days[iContainerID])
        , ALLNOBLANKROW(ShippedPallet_10Days[iContainerID])
        , ALLNOBLANKROW(PalletMovement_10Days[iContainerID], PalletMovement_10Days[Timestamp])
    )
    return if(
        ShippedPallet_10Days[Load Released Datetime] <> BLANK()
        && firstMovement >= RELATED(ShippedLoad_10Days[First Order Released Datetime])
        && ShippedPallet_10Days[First Location] = "Queue 266", "Waited on LP", "Did not wait"
    )

I tried making the above 4 changes to all of the custom columns in the entire report; no effect on the dependencies of this new column. Then I disabled the Time Intelligence feature, but that didn't have any effect.

 

Then I converted all  

 

ALLEXCEPT(Alex_ShippedPallet_10Days, 
    Alex_ShippedPallet_10Days[Waited on Case Pick]
)

 

... to ...

 

ALLEXCEPT(Alex_ShippedPallet_10Days, 
    Alex_ShippedPallet_10Days[Waited on Case Pick], 
    Alex_ShippedPallet_10Days[Waited on LP],
    Alex_ShippedPallet_10Days[Was Manually Retrieved]
)

 

... for all calculated columns on ShippedPallet (removing the appropriate field when editing these three calculated columns, of course).

 

Again, no affect to the dependencies as determined by DAS Studio, except for the fact that removing Time Intelligence removed the date tables from the result set.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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