Using filters as union in Power BI

[Anonymous]

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?

[v-eachen-msft · Accepted 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.

[amitchandak]

@Anonymous , use or in filter with all

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

[jthomson]

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