Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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
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
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |