Forum Discussion

quintinh's avatar
quintinh
Frequent Visitor
3 years ago
Solved

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.

 

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's avatar
    quintinh
    3 years ago

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

6 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      quintinh
      Frequent Visitor
      3 years ago

      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's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        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's avatar
      quintinh
      Frequent Visitor
      3 years ago

      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's avatar
    quintinh
    Frequent Visitor
    3 years ago

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