cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Using filters as union in Power BI

Hi @ftl ,

 

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
Highlighted
Solution Sage
Solution Sage

Re: Using filters as union in Power BI

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

Highlighted
Super User IV
Super User IV

Re: Using filters as union in Power BI

@ftl , use or in filter with all

 

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Re: Using filters as union in Power BI

Hi @ftl ,

 

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors