Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

ALLNOTSELECTED with EXCEPT but not using FILTER Function

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:)

1 REPLY 1
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors