Using CROSSFILTER to work around Single Direction Relationship

[Anonymous]

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

[daxer-almighty · Accepted solution]

@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"
        )
        
    )

[Anonymous]

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.