Distinct Count of Cases Based on Multiple Rows Dispositions

[ARomain]

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. 

[AlexisOlson · Accepted solution]

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]

That is a very elegant solution @AlexisOlson !

[freginier]

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)

[bcdobbs]

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 )