Filter with And Condition using slicer

[venkatnitt84]

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

[v-yiruan-msft · Accepted solution]

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

[jagan2678]

Hi @v-yiruan-msft ,

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

[amitchandak]

@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])