Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
nemo189
Frequent Visitor

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.

nemo189_0-1703073150125.png

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.

nemo189_2-1703073655050.png

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

 

 

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@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

Fowmy_0-1703101188021.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

3 REPLIES 3
Fowmy
Super User
Super User

@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

Fowmy_0-1703101188021.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Jihwan_Kim
Super User
Super User

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

Jihwan_Kim_1-1703100862569.png

 

 

Jihwan_Kim_0-1703100837807.png

 

 

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
    )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


Jayaselvan
Helper II
Helper II

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.