Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Using CROSSFILTER to work around Single Direction Relationship

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's avatar
    daxer-almighty
    5 years ago

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

     

2 Replies

  • daxer-almighty's avatar
    daxer-almighty
    Solution Sage
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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.