topic Re: Dynamic filteration of table in DAX Commands and Tips

Dynamic filteration of table

nemo189 — Wed, 20 Dec 2023 18:45:39 GMT

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 as a measure. But unable to achieve this . Kindly help

Re: Dynamic filteration of table

Jayaselvan — Wed, 20 Dec 2023 19:15:16 GMT

Hi,

Hope this helps, if not please share the pbi file with the dataset loaded.
UNION(
FILTER(Table, Table[Column1] = SELECTEDVALUE(Table[Column1])),
FILTER(
Table,
NOT (
Table[Column1] = SELECTEDVALUE(Table[Column1])
&& Table[Column3] IN VALUES(Table[Column3])
)
)
)

Re: Dynamic filteration of table

Jihwan_Kim — Wed, 20 Dec 2023 19:34:55 GMT

Hi, I am not sure if I correctly understood your question, but please try something like below if it suits your requirement.

filter measure: = VAR _selectslicer = VALUES ( Slicer[Col 1] ) VAR _selectcol3 = SUMMARIZE ( FILTER ( ALL ( Data ), Data[Col 1] IN _selectslicer ), Data[Col 3] ) RETURN SWITCH ( TRUE (), NOT ISFILTERED ( Slicer[Col 1] ), 1, MAX ( Data[Col 1] ) IN _selectslicer, 1, NOT ( MAX ( Data[Col 1] ) IN _selectslicer ) && NOT ( MAX ( Data[Col 3] ) IN _selectcol3 ), 1, 0 )

Re: Dynamic filteration of table

Fowmy — Wed, 20 Dec 2023 19:40:44 GMT

nemo189

Create the following measure and assign it in the visual filter of the table viz:

Flag = VAR __C1 = SELECTEDVALUE( Col1_Table[Col1]) VAR __ExcludeC3 = CALCULATETABLE( VALUES( Table1[Col3] ) , Table1[Col1] = __C1 , REMOVEFILTERS(Table1) ) RETURN SWITCH( TRUE(), SELECTEDVALUE( Table1[Col1] ) = __C1, 1, NOT (SELECTEDVALUE( Table1[Col3] ) IN __ExcludeC3), 1, 0 )

File is attached