To get slicer selected values as a list to put it under IN ()

[ak1207]

Hi ,

How can we get selected list of values from slicer in {"val_1","val_2","val_3"} format, so that the list can be put under IN () operator ?

Currently using a measure,

Measure = (CONCATENATEX ( VALUES ( Values_Column[Values]),[Values] , ",")) and getting val_1,val_2,val_3 as value but not able to use under IN() operator.

TIA.

[v-jialluo-msft · Accepted solution]

^{Hi @ak1207 ,}

^{Please follow these steps:}

^{(1) Create a new measure}

FLAG = 
IF (
    ISFILTERED ( Slicer_Table[Col_2] ),
    IF (
        MAX ( 'Table 1'[Col_1] )
            IN EXCEPT (
                SUMMARIZE (
                    FILTER ( 'Table 1', [Col_2] IN VALUES ( Slicer_Table[Col_2] ) ),
                    [Col_1]
                ),
                SUMMARIZE (
                    FILTER (
                        ALL ( 'Table 1' ),
                        NOT ( [Col_2] ) IN VALUES ( 'Slicer_Table'[Col_2] )
                    ),
                    [Col_1]
                )
            ),
        1
    )
)

^{(2) Add filtering}

^{(3)Final output}

^{Best Regards,}

^{Gallen Luo}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[ak1207]

Thanks @v-jialluo-msft for the solution. 🙂

[amitchandak]

@ak1207 , In case you are more than one selected then you can use in values(Table[values])

example

filter(Table, Table[Value] in values(Table[Values]))

[ak1207]

Thanks @amitchandak for the response. However, the solution provided in not working for me.

Please find below problem exploration with the usecase/example :

Table 1
Col_1	Col_2
A	1
B	1
B	2
C	1
C	2
C	3
D	1

Slicer_Table

Col_2

1

2

3

If selected value(s), from slicer, only the Col_1 data which belong to the selected value and NOT anything else should display.

View : Table with Table_1[Col_1] vs Measure and a slicer with Slicer_Table[Col_2]

For example,

if selected 1, only A & D should show

if selected 2, no one should show (since 2 mapped to B & C but they belong to other Col_2 data as well)

if selected 1 & 2, A,B & D should show

if selected 1,2 & 3 ; All should show

WORKING WITH HARDCODED VALUE 1 & 2 ::

Measure_x = 

CALCULATE (

    DISTINCTCOUNT(Table_1[Co1_1]),

    FILTER(VALUES (Table_1[Col_1]),

    CALCULATE ( DISTINCTCOUNT (Table_1[Col_2 ]), not(Table_1[Col_2]) in {"1","2"}  ) = 0

    )

)

NOT WORKING WITH slicer selected VALUE 1 & 2 ::

Measure_y= 

CALCULATE (

    DISTINCTCOUNT(Table_1[Co1_1]),

    FILTER(VALUES (Table_1[Col_1]),

    CALCULATE ( DISTINCTCOUNT (Table_1[Col_2 ]), not(Table_1[Col_2]) in VALUES(Slicer_Table[Col_2])  ) = 0

    )

)

Please help with the above example.