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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors