Forum Discussion

jhenscheid's avatar
jhenscheid
Frequent Visitor
9 years ago
Solved

Measure on-time delivery performance and slice by origin location

I have transporation data and am displaying the carriers' performance for delivery.  However, I am trying to slice the data by where the load/trip originates.  Because I am measuring delivery perform...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi jhenscheid,

     

    In source table, create a calculated column that returns the original location for each Carrier per LoadID.

    Original lacation =
CALCULATE (
    LASTNONBLANK ( 'delivery performance'[Pick Location], 1 ),
    FILTER (
        ALLEXCEPT (
            'delivery performance',
            'delivery performance'[LoadID],
            'delivery performance'[Carrier]
        ),
        'delivery performance'[Type] = "pick"
    )
)

     

    Create an extra table which lists all distinct values of Pick Location.

    Pick location table = VALUES('delivery performance'[Pick Location])

     

    Create a one to many relationship between this new table and source table based on [Original Location].

     

    In report page, rather than adding [Pick Location] from source table into slicer, you should add 'Pick location table'[Pick Location] into slicer. Then, when you select pick location item, the visual won't return none.

     

    Best regards,
    Yuliana Gu

     

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi jhenscheid,

 

In source table, create a calculated column that returns the original location for each Carrier per LoadID.

Original lacation =
CALCULATE (
    LASTNONBLANK ( 'delivery performance'[Pick Location], 1 ),
    FILTER (
        ALLEXCEPT (
            'delivery performance',
            'delivery performance'[LoadID],
            'delivery performance'[Carrier]
        ),
        'delivery performance'[Type] = "pick"
    )
)

 

Create an extra table which lists all distinct values of Pick Location.

Pick location table = VALUES('delivery performance'[Pick Location])

 

Create a one to many relationship between this new table and source table based on [Original Location].

 

In report page, rather than adding [Pick Location] from source table into slicer, you should add 'Pick location table'[Pick Location] into slicer. Then, when you select pick location item, the visual won't return none.

 

Best regards,
Yuliana Gu

 

jhenscheid's avatar
jhenscheid
Frequent Visitor
9 years ago

This worked exactly as needed!!  Thank you for your help.  Much appreciated!