Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Say I've got 3 different date range filters that work on different fields to filter a table. Is it possible to get the to produce a union of the data from the 3 filters.
Easier to explain with an example:
All 3 date columns are in the same table visual but there are different slicers that will drive the filtering for each column.
At the moment, if I apply one filter it will adjust the available date ranges to the other filters according to what has been filtered out from the first filter. However, I'd like to be able to use all 3 filters to get some sort of union of the data, e.g. the final output would be:
Is this achievable in Power BI?
Solved! Go to Solution.
Hi @Anonymous ,
You could create three distinct date tables as slicers.
Then refer to the following measure:
Measure =
VAR a =
IF ( SELECTEDVALUE ( 'Table'[Date1] ) IN ALLSELECTED ( 'Slicer 1' ), 0, 1 )
VAR b =
IF ( SELECTEDVALUE ( 'Table'[Date2] ) IN ALLSELECTED ( 'Slicer 2' ), 0, 1 )
VAR c =
IF ( SELECTEDVALUE ( 'Table'[Date3] ) IN ALLSELECTED ( 'Slicer 3' ), 0, 1 )
RETURN
a * b * c
Then add this measure into filter pane and set it to show 0.
Here is my test file for your reference.
Hi @Anonymous ,
You could create three distinct date tables as slicers.
Then refer to the following measure:
Measure =
VAR a =
IF ( SELECTEDVALUE ( 'Table'[Date1] ) IN ALLSELECTED ( 'Slicer 1' ), 0, 1 )
VAR b =
IF ( SELECTEDVALUE ( 'Table'[Date2] ) IN ALLSELECTED ( 'Slicer 2' ), 0, 1 )
VAR c =
IF ( SELECTEDVALUE ( 'Table'[Date3] ) IN ALLSELECTED ( 'Slicer 3' ), 0, 1 )
RETURN
a * b * c
Then add this measure into filter pane and set it to show 0.
Here is my test file for your reference.
@Anonymous , use or in filter with all
calculate([Measure], filter(all(Table) ,<First date filter> || <second date filter> || <third date filter> ))
I think this can be done, but you're going to need to look at this in reverse - this is equivalent to finding the rows where column 1 is not in filter 1, column 2 is not in filter 2 and column 3 is not in filter 3, then return the opposite.
You'd probably need to set up your "filters" as disconnected tables, use some selectedvalues formula to return the results, then count the rows in each result that match your columns. Add the three results up and filter out zero and you should get the final result
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |