topic Re: Filter Out Rows Given List of Values in DAX Commands and Tips

Filter Out Rows Given List of Values

quintinh — Tue, 18 Oct 2022 15:00:33 GMT

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.

Re: Filter Out Rows Given List of Values

tamerj1 — Tue, 18 Oct 2022 15:13:30 GMT

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"} )

Re: Filter Out Rows Given List of Values

quintinh — Tue, 18 Oct 2022 17:31:10 GMT

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 ...

Re: Filter Out Rows Given List of Values

quintinh — Tue, 18 Oct 2022 17:34:37 GMT

Thought of another permutation to try ...

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

Still no change

Re: Filter Out Rows Given List of Values

tamerj1 — Tue, 18 Oct 2022 18:07:45 GMT

quintinh

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

Re: Filter Out Rows Given List of Values

quintinh — Tue, 18 Oct 2022 18:37:48 GMT

Screenshot of filter pane ...

Screenshot of data ...

What does that tell us?

Re: Filter Out Rows Given List of Values

quintinh — Mon, 24 Oct 2022 13:17:57 GMT

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'.