Dynamic filter selection

[nemo189]

So I have a table as shown.Now, I have a slicer with column 1 values in it. I am creating a table visual to show all the columns.

If I select value A in slicer , then the output should be all the rows for A , along with all the values where col 3 value is not equal to any col3 value of A. When no filter is selected then all entire table should be shown.

.Not sure how to achieve this, I was thinking to put a flag. But unable to achieve this . Kindly help

[Anonymous · Accepted solution]

Hi @nemo189 ,

Please refer to my pbix file to see if it helps you.

Create a table with col1.

Then create the following measures.

Measure =
VAR _1 =
    SELECTEDVALUE ( 'Table 2'[col1] )
VAR _2 =
    IF ( _1 = SELECTEDVALUE ( 'Table'[col1] ), MAX ( 'Table'[col3] ), BLANK () )
RETURN
    _2

result =
VAR _a =
    SELECTEDVALUE ( 'Table 2'[col1] )
VAR _b =
    SELECTEDVALUE ( 'Table'[col1] )
VAR _c =
    SELECTEDVALUE ( 'Table'[col3] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [col1] = _a )
VAR tmp1 =
    CALCULATETABLE ( VALUES ( 'Table'[col3] ), tmp )
VAR tmp2 =
    FILTER ( ALL ( 'Table' ), NOT ( [col3] IN tmp1 ) )
VAR tmp3 =
    CALCULATETABLE ( VALUES ( 'Table'[col3] ), tmp2 )
RETURN
    IF ( OR ( _b = _a, _c IN tmp3 ), 1, 0 )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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