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

UNION of results from two slicers

DAX.PNG

Hi,

 

My problem is that I have one table which I need to filter using two slicers. The goal is to be able to choose any number of values from column A(using first slicer) and any number of values from column B(using second slicer). As a result I should get UNION/OR of these results.

 

Requirements:

*Slicers should not filter themselves. If I choose something from slicer one, I should still have available all values from slicer two. 

*I should be able to get results using only one of the slicers or both of them:

If I choose A1 from the first slicer, I should get only A1 as result.

If I choose B2 from the second slicer, I should get A1 and A2.

If I choose A1 from the first slicer and B3 from the second one, I should get A1 and A2.

*Result should be empty by default(when nothing is selected)

 

What I have tried:

IsInSelection = CALCULATE(
    COUNTROWS(Table),
    FILTER(
        'Table',
        OR(
            Table[A] IN VALUES(TableFilterOne[A]),
            Table[B] IN VALUES(TableFilterTwo[B])
        )
    )
)

With additional filter tables used for slicers. However, it doesn't work when I want to select values only from one of the slicers.

IsInSelection = IF ( SELECTEDVALUE ( TableFilterOne[A] ) IN VALUES ( Table[A])||SELECTEDVALUE ( TableFilterTwo[B] ) IN VALUES ( Table[B])|1|0)

This approach works only for one selection. So It breaks when I am trying to select multiple values from slicers and I cannot find out how to improve it.

 

Thank you for any advice!

1 ACCEPTED SOLUTION

So you could expand the initial approach, but with more than 2 filter tables the logic would get quite convoluted.

 

Below is a simpler pattern that should be expandable to as many filter tables as you like although I'm not sure what impact these "OR" style filters will have on performance as I only have a handful rows in a small test model

 

IsInSelection2 = 
var _aset = ALLSELECTED(TableFilterOne[A])   
var _rows =        
COUNTROWS(   
    FILTER(
        'Table',
        IF(ISFILTERED('TableFilterOne'[A]), 'Table'[A] IN VALUES(TableFilterOne[A]))
        || IF(ISFILTERED('TableFilterTwo'[B]),'Table'[B] IN VALUES(TableFilterTwo[B]))
        || IF(ISFILTERED('TableFilterThree'[C]), 'Table'[C] IN VALUES(TableFilterThree[C]),FALSE())
    )
)
return _rows 

View solution in original post

3 REPLIES 3
d_gosbell
Super User
Super User

So for this to work you would need to make sure that TableFilterOne and TableFilterTwo do not have a relationship to Table and filters from related tables are always effectively combined as a logical AND.

 

The other issue you will hit is that VALUES will return all everything from the filter tables if nothing is filtered, so we need to add some additional logic to check if one or both of the filter tables have values selected.

 

Given that something like the following should work

 

IsInSelection = 
var _aset = ALLSELECTED(TableFilterOne[A])   
var _rows =        
COUNTROWS(   
    FILTER(
        'Table',
        IF (ISFILTERED(TableFilterOne[A]) && ISFILTERED(TableFilterTwo[B]),
            OR(
                'Table'[A] IN VALUES(TableFilterOne[A]),
                'Table'[B] IN VALUES(TableFilterTwo[B])
            ),
            IF (ISFILTERED(TableFilterOne[A]), 'Table'[A] IN VALUES(TableFilterOne[A]),
                IF (ISFILTERED(TableFilterTwo[B]), 'Table'[B] IN VALUES(TableFilterTwo[B]))
            )
        )
    )
)
return _rows 

 

Anonymous
Not applicable

Thank you so much! Worked perfectly in case of two slicers. However, now I need to expand it to 5 slicers...(so I will have additional columns C, D, E but still only A in results). Do you have some advice on how to tackle this? With this solution I would have to expand it with a lot of IF and OR statements. 

 

 

 

 

So you could expand the initial approach, but with more than 2 filter tables the logic would get quite convoluted.

 

Below is a simpler pattern that should be expandable to as many filter tables as you like although I'm not sure what impact these "OR" style filters will have on performance as I only have a handful rows in a small test model

 

IsInSelection2 = 
var _aset = ALLSELECTED(TableFilterOne[A])   
var _rows =        
COUNTROWS(   
    FILTER(
        'Table',
        IF(ISFILTERED('TableFilterOne'[A]), 'Table'[A] IN VALUES(TableFilterOne[A]))
        || IF(ISFILTERED('TableFilterTwo'[B]),'Table'[B] IN VALUES(TableFilterTwo[B]))
        || IF(ISFILTERED('TableFilterThree'[C]), 'Table'[C] IN VALUES(TableFilterThree[C]),FALSE())
    )
)
return _rows 

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.