ALLNOTSELECTED with EXCEPT but not using FILTER Function

[Anonymous]

Hi all, I have a two part question.

I am trying to write an ALLNOTSELECTED schema as mentioned below.

Opposite of ALLSELECTED? (ALLNOTSELECTED) 

For some reason, the function does not work as expected in my DAX code. I have found at least two ways of writing it, but it uses the Filter function. I would like to avoid the filter function for the various reasons mentioned here.

Can anyone help me come up with a way I can use my row context (driver_id) as essentially a reverse filter without using the FILTER function? I want it to include every driver_id for a specific trip_id except for a the driver_id in my row context.

I'm attaching a sample PBIX for reference. Thanks so much in advance! Will make sure to mark as solution if it is helpful:)

[OwenAuger]

Hi @Anonymous 

Thanks for posting your PBIX - it made it easy to confirm the logic you want to apply 🙂

I would suggest writing something like this:

Early Pct Not this Driver EXCEPT ALL VALUES = 
CALCULATE (
    [Early Pct],
    EXCEPT ( ALL ( Trips[driver_id] ), VALUES ( Trips[driver_id] ) )
)

I did some performance comparisons with your existing measures (after increasing the size of your table by 1000 times) and this does appear to outperform the others using FILTER.

This is pretty similar to your measure Early Pct Not this Driver with FILTER FUNCTION 2 but uses VALUES rather than a FILTER expression to access the list of visible driver_id values, so it would also handle cases of multiple selection of driver_id.

Would this work for you?

Regards,

Owen