Forum Discussion
Filter with And Condition using slicer
I have a requirement where i need to filter the data based on the selected Value using "AND" condition.
Below is an example for the requirement.
If user selects "ABC" and "DEF" value , only District "A" and District "C" should be displayed.
District "B" should not be displayed as it doesnot have Value "DEF".
Basically i need to make sure all selected value is checked instead just one value for the given level.
Let me know if any one have achieved using DAX or any other method.
| District | Option | Amt | Slicer Selection | ||
| A | ABC | 1000 | ABC | ||
| A | DEF | 1000 | DEF | ||
| A | KKK | 1000 | KKK | ||
| B | ABC | 1000 | ZZZZ | ||
| B | ZZZZ | 1000 | EEEE | ||
| B | EEEE | 1000 | MMM | ||
| C | ABC | 1000 | |||
| C | DEF | 1000 | |||
| C | MMM | 1000 |
Hi venkatnitt84 ,
You can follow the below steps to achieve it:
1. Create a measure as below
Include all selections? = VAR _sel = ALLSELECTED ( 'Table'[Option] ) VAR _countofOp = CALCULATE ( DISTINCTCOUNT ( 'Table'[Option] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[District] = MAX ( 'Table'[District] ) && 'Table'[Option] IN _sel ) ) VAR _district = CALCULATE ( MAX ( 'Table'[District] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[District] = MAX ( 'Table'[District] ) && 'Table'[Option] IN _sel ) ) RETURN IF ( COUNTROWS ( _sel ) = _countofOp, 1, 0 )2. Then create a table with District field and add filter with new created measure=1
Best Regards
Rena
3 Replies
Hi venkatnitt84 ,
You can follow the below steps to achieve it:
1. Create a measure as below
Include all selections? = VAR _sel = ALLSELECTED ( 'Table'[Option] ) VAR _countofOp = CALCULATE ( DISTINCTCOUNT ( 'Table'[Option] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[District] = MAX ( 'Table'[District] ) && 'Table'[Option] IN _sel ) ) VAR _district = CALCULATE ( MAX ( 'Table'[District] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[District] = MAX ( 'Table'[District] ) && 'Table'[Option] IN _sel ) ) RETURN IF ( COUNTROWS ( _sel ) = _countofOp, 1, 0 )2. Then create a table with District field and add filter with new created measure=1
Best Regards
Rena
Hi Anonymous ,
Thank you for your solution. I was doing something similar and yours helped me. But there is one issue in my case as my data model is like this,
Include all selections for office? =
VAR _selected =
ALLSELECTED ( 'dim'[year] )
VAR _options =
CALCULATE (
DISTINCTCOUNT ( 'office'[year] ),
FILTER (
ALLSELECTED ( 'office' ),
'office'[office] = MAX ( 'office'[office] )
&& 'office'[year] IN _sel
)
)
RETURN
IF ( COUNTROWS ( _selected ) = _options, 1, 0 )
So here the above measure which i created based on urs, is working for the table with active realtionship i.e year column of office table is in active relation with year column of dimdate.
But, when i try to do the same for the another table hotel which has an inactive relationship, i dont get the results,
Include all selections for hotel? =
VAR _selected =
ALLSELECTED ( 'dim'[year] )
VAR _options =
CALCULATE (
DISTINCTCOUNT ( 'hotel'[year] ),
FILTER (
ALLSELECTED ( 'hotel' ),
'hotel'[hotel] = MAX ( 'hotel'[hotel] )
&& 'hotel'[year] IN _sel
)
)
RETURN
IF ( COUNTROWS ( _selected ) = _options, 1, 0 )
help me with this. i was like trying to use the userelationship function,, but i dont know where to use this function in the above measure.
Thanks in advance
venkatnitt84 , Try new measures like
measure =
var _cnt = count(allselected(Table), Table[District])
return
countX(filter(summarize(Table, Table[District], "_1", count(Table[District]), "_2", sum(Table[Amit])), [_1]>=_cnt),[District])Amount =
var _cnt = count(allselected(Table), Table[District])
return
countX(filter(summarize(Table, Table[District], "_1", count(Table[District]), "_2", sum(Table[Amit])), [_1]>=_cnt),[_2])
