NOT IN with a list of values

DAX DAX Calculations
1

Problem: We’re a trucking company. Driver turnover is a big issue in our industry. I’m doing some driver retention analytics. I’m trying to create a driver count measure but need to filter out terminations with specific reason codes as we do not count those in our turnover percentage.

Where I’m at: I used Sam’s videos on staff counting and staff turnover to get this far. I’m new at DAX so everything is by example then reverse engineering it to learn how it works. Mr. Googlepants has told me that in order to remove records with specific codes I need to use “NOT” and “IN”.

This is the permutation I stopped with and decided to just post here …

Driver Count = 
CALCULATE (
    COUNTROWS ( Drivers ),
    FILTER (
        VALUES ( 'Drivers'[Hire Date] ),
        'Drivers'[Hire Date] <= MAX ( Dates[Date] )
    ),
    FILTER (
        VALUES ( 'Drivers'[Termination Date] ),
        OR(
            Drivers[Termination Date] >= MIN ( Dates[Date] ),
            ISBLANK ('Drivers'[Termination Date] )
        )
    ),
    NOT 'Drivers'[Termination Code] IN {"LEFTOR","FAILIN","FAILRT","FAILWR","STLEFT","STFLRT","STFLIN","NEVER"}
)

I say this permutation because I’ve been through many using different sources and their examples. Yeah, I’m kinda stabbin in the dark hoping some version will work.

Could someone point me in the right direction?

An anonymized PBIX file is attached. The ‘EDNA Driver Count’ sheet is where I’m working. The measure is ‘Driver Count’
EDNA Driver Retention.pbix (332.2 KB)

2

Hello @qholmberg . I have used IN before. When I want to calculate something specific, use IN to single out specific work codes or in your case termination.

I think you would want to delete NOT. You are looking for driver count the are in these codes…correct?

3

@Paul.Gerber … I want the drivers with these term codes excluded from the count.

4

Hello. I looked at your report. Why not just total all drivers, calculate total drivers that have been terminated, then take the difference?

9,111 total drivers to date,
Drivers Terminated is 1251

Drivers remaining is 7860

I used this concept with my billable hours and work codes for technicians.

Attached is the PBIX
EDNA Driver Retention -Gerber.pbix (331.8 KB)

5

@Paul.Gerber … I’ll look into it. I’m also playing with adding a field to my file so can use something other than the NOT and IN.

Thank you for the help.

1 Like
6

Yeah from what I’ve read, the state NOT is used in cases of NOTISBLANK.

Let me know how it plays out. I’ll keep checking it out on my end.

Paul

7

An example,

MEASURE NOT IN =
CALCULATE (
SUM ( Table1[Value] ),
FILTER (
Table1, NOT ( Table1[CAT LEVEL] ) IN { 2,3 }
)
)

8

@Paul.Gerber … that last FILTER with the NOT looks a lot like what I’ve been doing.

No matter. I’ve resolved it. I went back to the SQL that creates this table and added a Y/N column for ‘Activated’. This tells me if they made it out of orientation or not. I then filter on Activated = “Y”. Boom … there it is.

Thank you for your assistance, Paul.

1 Like
9

@qholmberg that is great to hear. Sometimes it is an easy fix along the way just have to chase it down. I’ve run into that several times before.

Best to you!

Paul

10

@qholmberg if you will mark the response as a solution that’'ll be great. Take care

1 Like
11

Thanks for working on a solution on this post @Paul.Gerber

Hello @qholmberg did the response above solve your query?

Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’