Forum Discussion
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
- AnonymousNot 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.