Filter Out Rows Given List of Values

[quintinh]

Background: We're a trucking company. Driver retention is a big issue for us so, I'm creating some retention analytics for the company to use. I'm working on a count of active drivers given a time context. With the help of some online research, I have the count almost working.

Problem: There is a termination code in our 'Drivers' file. A subset of those codes are for drivers that did not make it out of orientation and, therefore, are never counted as an active driver. I can't remove them at the Power Query level as I need them for other retention related analytics. I need to eliminate them with DAX. I'm trying to add that to the 'Driver Count' measure.

Mr. Googlepants tells me I should be able to use 'NOT' with 'IN' to make this happen. I've yet to be successful with a variety solution examples. This is my latest permutation ...

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] )
        )
    ),
        FILTER(
        'Drivers',
        NOT 'Drivers'[Termination Code] IN {"LEFTOR","FAILIN","FAILRT","FAILWR","STLEFT","STFLRT","STFLIN","NEVER"}
    )
)

The third filter statement is where I'm having trouble ...

        FILTER(
        'Drivers',
        NOT 'Drivers'[Termination Code] IN {"LEFTOR","FAILIN","FAILRT","FAILWR","STLEFT","STFLRT","STFLIN","NEVER"}
    )

I've tried it without filter and with 'VALUES' as the others filters are done (I understand the logic but not all of the syntax of those, yet).

This is what I see as I test the measure. Clearly, the items in the list are still present.

If you wish to view the PBIX, I have created an anonomized version. The 'Driver Count' tab is where the screenshot above was taken from.

Driver Retention Anonymized 

If anyone can point me in a direction to learn what I need to do here, it would be greatly apprecieated.

[quintinh · Accepted solution]

In the event anyone else runs across this, this is how I resolved my issue.

I never did figure out how to use NOT with IN to filter out the reason codes listed in the example.

I returned to the SQL that created this data and added a Y/N column of "Activated". Any driver with one of the codes listed was never activated. I then filtered by only including those with a 'Y'.

[tamerj1]

Hi @quintinh 

NOT in this case shall be used as a function not as an operator therefore you need to wrap its argument with brackets 

FILTER(
        'Drivers',
        NOT 'Drivers'[Termination Code] IN {"LEFTOR","FAILIN","FAILRT","FAILWR","STLEFT","STFLRT","STFLIN","NEVER"}
    )

[quintinh]

Thought of another permutation to try ...

    FILTER(
        'Drivers',
        NOT('Drivers'[Termination Code] IN {"LEFTOR","FAILIN","FAILRT","FAILWR","STLEFT","STFLRT","STFLIN","NEVER"})
    )
)

Still no change

[quintinh]

@tamerj1 ... That was a previous permutation until some info from SQLBI told me I did not need them.

I removed them so now the measure looks like ...

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] )
        )
    ),
    FILTER(
        'Drivers',
        NOT('Drivers'[Termination Code]) IN {"LEFTOR","FAILIN","FAILRT","FAILWR","STLEFT","STFLRT","STFLIN","NEVER"}
    )
)

Is that syntactically correct?

The change did not change the results ...

[tamerj1]

@quintinh 

Try to place the measure in the filter pane of the right side table and select "is not blank " then apply the filter. 

[quintinh]

Screenshot of filter pane ...

Screenshot of data ...

What does that tell us?