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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors