topic Re: Using CROSSFILTER to work around Single Direction Relationship in DAX Commands and Tips

Using CROSSFILTER to work around Single Direction Relationship

Anonymous — Fri, 02 Apr 2021 15:50:13 GMT

Data Model

WarehouseLocation - Table of locations within a warehouse

WarehousePackage - Table of warehouse pick locations by package

Single Direction relationship from WarehouseLocation to WarehousePackage.

Desired Outcome

A table visual that counts the number of packages that start/end in each warehouse aisle, excluding Aisle = "MISSING".

Example for one Package (starts in A, ends in E):

Package ID	Location Desc	Aisle
10001	A4	A
10001	A8	A
10001	B1	B
10001	B3	B
10001	B6	B
10001	E3	E
10001	E4	E
10001	E5	E
10001	E8	E

For all packages in the example dataset/report:

Aisle	FirstAisle	LastAisle
A	41	0
B	7	0
C	1	2
D	1	3
E	0	45

Problem Statement

I don't have an option to modify the dataset, so I need to work around the single direction cross filter relationship. When I assemble the table visual, the Location is still filtering even when I attempt to control for it, essentially counting all packages that hit an aisle, and not just the First and Last.

DAX Measure

_StartCount = VAR __Summary = SUMMARIZE ( FILTER(WarehousePackage, WarehousePackage[Location Desc] <> "MISSING"), WarehousePackage[Package ID], "FirstAisle", CALCULATE (MIN(WarehouseLocation[Aisle]), CROSSFILTER(WarehouseLocation[Location ID], WarehousePackage[Location ID], Both)) ) VAR __Count = CALCULATE ( COUNTROWS ( FILTER ( __Summary, [FirstAisle] = SELECTEDVALUE ( WarehouseLocation[Aisle] ) ) ), CROSSFILTER(WarehouseLocation[Location ID], WarehousePackage[Location ID], Both) ) RETURN __Count

PBIX - https://www.dropbox.com/s/jww9rxk43x5xsnj/Warehouse.pbix?dl=0

Re: Using CROSSFILTER to work around Single Direction Relationship

daxer-almighty — Fri, 02 Apr 2021 23:32:32 GMT

Anonymous

I don't have time to optimize this measure... but you might not even need it. To use the measure correctly (and understand its behaviour) you have to have a correct dimensional model and follow Best Practices of dimensional modeling in PBI (for instance, you should never slice by attributes in the fact table apart from a degenerate dimension). It's up to you to create correct models and take precautions to guide users to use them correctly.

[First Isle Count] = var vAisleIsInScope = ISINSCOPE( WarehouseLocation[Aisle] ) var vCurrentAisle = SELECTEDVALUE( WarehouseLocation[Aisle] ) var vMissingAisleNotInScope = vCurrentAisle <> "missing" RETURN IF( vAisleIsInScope && vMissingAisleNotInScope, // Need to find all the packages // in the current context that have // the selected isle as the first one. // This must happen after the removal // of all filters from WarehouseLocation // and exclusion of the missing isle. All // other filters ARE HONORED. CALCULATE( SUMX( DISTINCT( WarehousePackage[Package ID] ), 1 * CALCULATE( // Change MINX to MAXX to have the // other measure, Last Isle Count. vCurrentAisle = MINX( SUMMARIZE( WarehousePackage, WarehouseLocation[Aisle] ), WarehouseLocation[Aisle] ) ) ), REMOVEFILTERS( WarehouseLocation ), WarehouseLocation[Aisle] <> "missing" ) )

Re: Using CROSSFILTER to work around Single Direction Relationship

Anonymous — Sat, 03 Apr 2021 00:07:09 GMT

Thank you for the detailed reply! Completely agree that this model does not follow best practices for this use case; it was developed for a more general self-service option for less technical end users.