Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Using filters as union in Power BI

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:

 

PowerBi-Example.PNG

 

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:

 

PowerBi-Output.PNG

 

Is this achievable in Power BI?

1 ACCEPTED SOLUTION
v-eachen-msft
Community Support
Community Support

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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

3 REPLIES 3
v-eachen-msft
Community Support
Community Support

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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
amitchandak
Super User
Super User

@Anonymous , use or in filter with all

 

calculate([Measure], filter(all(Table) ,<First date filter> || <second date filter> || <third date filter> ))

jthomson
Solution Sage
Solution Sage

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.