Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Distinct Count of Cases Based on Multiple Rows Dispositions

I am trying to distinct count the number of IDs that did not ship any item. It doesn't matter what the item was, just if an ID had ANY item that shipped or did not ship.

 

In my exmaple below, IDs 3 and 4 did not ship any item, so I am trying to figure out a measure that would bring back a result of 2 but I can't wrap my head around a measure. 

 

 

 

 

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    You could iterate through the ID values and check if there is exactly one distinct disposition equal to Not Shipped.

     

    COUNTROWS (
    FILTER (
        VALUES ( Table1[ID] ),
        CALCULATE ( SELECTEDVALUE ( Table1[Disposition] ) ) = "Not Shipped"
    )
)

     

4 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    You could iterate through the ID values and check if there is exactly one distinct disposition equal to Not Shipped.

     

    COUNTROWS (
    FILTER (
        VALUES ( Table1[ID] ),
        CALCULATE ( SELECTEDVALUE ( Table1[Disposition] ) ) = "Not Shipped"
    )
)

     

  • bcdobbs's avatar
    bcdobbs
    Icon for Community Champion rankCommunity Champion
    4 years ago

    I think this works.  I called the table Shipping:

    IDs Not Shipped = 

VAR ShippedIds = 
    CALCULATETABLE(
        VALUES ( Shipping[ID] ),
        Shipping[Disposition] <> "Not Shipped"
    )

VAR NotShippedIds = 
    CALCULATETABLE (
        VALUES ( Shipping[ID] ),
        Shipping[Disposition] = "Not Shipped"
    )

VAR NeverShippedIds =
    EXCEPT( NotShippedIds, ShippedIds )

RETURN
    COUNTROWS ( NeverShippedIds )
  • freginier's avatar
    freginier
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Create a flag column to check if an ID has been shipped if true then 0 and 1 if never shipped.

    Flag = 
VAR Total_shipped =
    CALCULATE ( count ( Table2[ID] ), FILTER( Table2, EARLIER(Table2[ID])=Table2[ID] ), Table2[Disposed]="Shipped" )
RETURN
    IF ( Total_shipped > 0, 0, 1 )

     Create a measure and count the ID never shipped (flag = 1)

    Measure = CALCULATE( DISTINCTCOUNT( Table2[ID] ), Table2[Flag] = 1)