CODEWITHSUNDEEP
sql-serverpowerbi
Nick Heidke
Nick Heidke

Reputation: 2847

Power BI Filter By Multiple Criteria using "AND"

Background

I've got a database that stores employees and their skills. What I want to do is be able to use a slicer to select multiple skills and show the employees that have ALL of the skills selected.

Problem

When I select multiple criteria, and "OR" is applied, showing me employees that have any of the skills selected. (See screenshot)

Power BI screenshot

Question

In the example above, how do I modify my report so that only Steve shows up?

What I've Tried

I've added a new measure based on the DAX that Tab Alleman has proposed, but I'm still seeing "OR" logic being implemented. Here's the DAX:

Employees with all skills = 
COUNTROWS (
FILTER (
    ADDCOLUMNS (
        VALUES ('dw Employee'[ADUserId]),
        "Skills", 
        CALCULATE (
            COUNTROWS ( VALUES ( 'dw Skill'[SkillName] ) ),
            CALCULATETABLE ( 'dw EmployeeSkill' )
        )
    ),
    [Skills] = COUNTROWS ( VALUES ( 'dw Skill'[SkillName] ) )
)
)

Upvotes: 1

Views: 3554

Answers (1)

Leonard
Leonard

Reputation: 2578

The measure Employees with all skills measure is fundamentally correct, but does not behave in the way you expect.

It is a measure, so by itself, it does not change how a table is filtered unless the measure is used in the table (either by adding the measure to the table as a column or by using the measure as a filter).

If you create a table with just Employee[Full Name] and your measure, you will see only Steve appear.

Employee full name only

Since you don't want the measure to appear, you can also put it in the Visual Level Filter and get the same effect:

Visual Level Filter

However, one tricky part of what you're doing is that you want to display the skill name too. As soon as you drag Skill Name onto the table, Joshua, Eric & other employees with either skill are going to reappear (because it messes with the row counts that the measure relies upon).

To address this, the measure you're using needs to be updated to ignore the Skill Name when calculating row counts:

Employees with all skills =
CALCULATE (
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( 'dw Employee'[ADUserId] ),
            "Skills", CALCULATE (
                COUNTROWS ( VALUES ( 'dw Skill'[SkillName] ) ),
                CALCULATETABLE ( 'dw EmployeeSkill' )
            )
        ),
        [Skills] = COUNTROWS ( ALLSELECTED( 'dw Skill'[SkillName] ) )
    )
),
ALLSELECTED ( 'dw Skill'[SkillName] )
)

All that I've really changed is the first and last lines. I've wrapped the entire measure in a new CALCULATE statement, and then at the very end changed the filter context to include all SkillNames from the 'dw Skill' table.

This then allows you to create a table with both FullName and SkillName and still only get Steve back:

Ignoring the Skill Name field

Note: I don't claim my DAX modification above is the most efficient or best solution to this problem.

Upvotes: 1

Related Questions