Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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.
Solved! Go to 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" )
I compared the dependencies for Waited on LP before and after making the following changes to all calculated columns on ShippedPallet:
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.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |