Measure to Show Customers who Have only One Order and of a Specific Type

[Darren204]

Hi I need to create a measure that will look up customers where they have only one order open for a specific service type. ie. Where this service type is the ONLY order they have.

Using my table below, I'd like to have a measure that returns only Michael's record when looking for customers that ONLY have Boogie 🙂

Customer	Service type	Order Number
Tito	Sunshine	123
Jermaine	Moonlight	456
Jermaine	Good Times	789
Jermaine	Boogie	111
Michael	Boogie	222

Probaly dead simple but I'm having a block...

Appreciate the help.

[v-cherch-msft · Accepted solution]

Hi @Darren204 

Please check below measure.You may use it in visual level filter to filter the table.

filter =
VAR count1 =
    CALCULATE ( COUNT ( Table2[Customer] ), ALLEXCEPT ( Table2, Table2[Customer] ) )
VAR count2 =
    CALCULATE (
        COUNT ( Table2[Customer] ),
        ALLEXCEPT ( Table2, Table2[Service type] )
    )
RETURN
    IF ( count1 = 1 && count2 > 1, 1 )

Regards,