Search code examples
powerbidaxpowerbi-desktop

Power BI change slicer logic from OR to AND

I have a table that looks like this:

| Person ID |  Date |  Day     | AM/PM |
|-—---------|-—-----|-—--------|-—-----|
|  1        | 5/6/24|   Friday | am    |
|  2.       | 5/6/24|   Friday | pm    |
|  2.       | 8/6/24|   Monday | am    |
|  3.       | 5/6/24|   Friday | am    |
|  4.       | 8/6/24|   Monday | am    |

I have a slicer that allows me to select two or more options, for example Friday PM and Monday AM.

This returns a row for all of the items that have a Friday PM and Monday AM.

What I want is only the items whose person ID meet both criteria: both Friday PM AND Monday AM. I am also getting rows for items that only have one of the criteria. This seems to imply the filter uses OR logic where I want AND logic. Is there anyway of configuring this effect.
I cannot filter the table as I need to return all the items from the database as sometime I may need to have different criteria ie Thursday AM and Tuesday AM.

Desired Output

In this scenario I only want to return ID 2 as it has both Friday pm and Monday am.

| Person ID |  Date |  Day     | AM/PM |
|-—---------|-—-----|-—--------|-—-----|
|  2.       | 5/6/24|  Friday  | pm    |
|  2.       | 8/6/24|  Monday   am    |

This table on the report is generated via the drop down menu and has several hundred rows with many repeated IDs but there will only ever be 2 IDs one for Friday and one for Monday in this example.

Solution

  • First, create a new column by merging the two columns containing the Weekday and Time period.
    For this example, I called this column Weekday_Time.

    Sample data

    After this, create a measure that will be used as a filter on your visual.

    IsUserFiltered = 
VAR _numberOfWeekdays = 
    IF(ISFILTERED(Users[Weekday_Time]), COUNTROWS(ALLSELECTED(Users[Weekday_Time])), 0)

RETURN 
    IF(_numberOfWeekdays = 0, 
        1,
        IF(DISTINCTCOUNT(Users[Weekday_Time]) = _numberOfWeekdays, 1, 0))

    This measure will check if the Weekday_Time column is filtered, and display all data if it isn't.
    If the Weekday_Time column IS filtered, it will return 1 for users whose Weekday_Time matches the ones selected.

    Now add this measure as a filter in your visual with the condition IS 1:

    Filter applied on the visual

    Since your visual will be using AND logic instead of OR, it is important to remember that it will only show data as long as the rows match.
    This means that if, for example, you choose Monday PM and Tuesday AM on your slicer and your table visual only has the UserId, it will display ID 1 since it matches both conditions (AND).
    If your table visual has more columns, it won't display any data, since the UserId will match both conditions, but the other columns won't (Monday PM doesn't match both Monday PM AND Tuesday AM).

    Filtered table example