Forum Discussion

nemo189's avatar
nemo189
Frequent Visitor
2 years ago
Solved

Dynamic filteration of table

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

 

 

Need Help
  • Fowmy's avatar
    Fowmy
    2 years ago

    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

     

3 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    2 years ago

    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

     

  • Jayaselvan's avatar
    Jayaselvan
    Icon for Helper II rankHelper II
    2 years ago

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

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    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
    )