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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
quintinh
Frequent Visitor

Filter Out Rows Given List of Values

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.

quintinh_0-1666104679583.png

 

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.

1 ACCEPTED SOLUTION
quintinh
Frequent Visitor

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

View solution in original post

6 REPLIES 6
quintinh
Frequent Visitor

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
Super User
Super User

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

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_1-1666114414337.png

 

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

quintinh_0-1666114163608.png

 

@quintinh 

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

Screenshot of filter pane ...

quintinh_0-1666118160572.png

Screenshot of data ...

quintinh_1-1666118213915.png

What does that tell us?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors