Forum Discussion

tonysantangelo's avatar
tonysantangelo
Frequent Visitor
3 years ago

Slicer with AND Logic with Multiple Tables

PBIX posted here. Below are my two data sources and the columns in each:

Events- DeviceId, EventDate

EligibilityDates- DeviceId, EligibilityDate

 

I would like to have a slicer with a list of eligibility dates. The desired behavior is that when multiple dates are selected, the events will be filtered to only events where the DeviceId was eligible on all of the dates selected. For example, if I were to select the months of January 21, February 21, and March 21 in the slicer, I would like the Events table to filter to these rows because only deviceIds 1 and 2 were eligible for all 3 of the selected months. 

Notice that slicers typically do not work like this. When multiple values are selected in a slicer, typically the data remaining meets the criteria of any of the slicer values (OR logic). Instead, I want the data to meet the criteria of all the slicer values (AND logic). 

 

What I've tried: I created a DAX measure for the EligibilityDates table that evaluates the slicer's selected value with AND logic. It evaluates to 1 when the DeviceId is eligible on all the selected dates and 0 otherwise. However, this measure is only available as a "filter on this visual" for visuals with EligibilityDates data. I can't apply the filter to the whole page.

 

Where I'm stuck: Updating the Events visual based on the Slicer selection on EligibilityDates. I have created a relationship between the two data sources such that EligibilityDates.Device ID filters Events.DeviceId. However, the filtering on Events still treats the Slicer as using OR logic. The "filter on this visual" with my mentioned DAX measure does not carry over to visuals with Events data. I believe I instead need a measure in the Events table that I could use for filtering, but I'm not sure how to write the DAX.

 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi tonysantangelo ,

     

    Here's a blog about applying AND logic, please kindly refer to

    Apply AND logic in the slicer - Microsoft Power BI Community

    The main idea is to compare the counts selected by the slicer with the counts in the table by category, if equal, it is sufficient and logical.

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.