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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
darrirafn
Frequent Visitor

Filter visual based on value not being blank

Hi there!

 

I have a table in the following format: 

Area; Birds; Woods; Buildings;

Name1; x; ; x;

Name2; x; x; x; 

Name3; ; ; x;

That is, the x specifies if there are Buildings in the area. 

I want to create a slicer ("Filter by group") that is ideally in the format of a dropdown and the user could select "Birds" to filter a visual based on only where the Birds column is not blank (or is x). I've tried a few measures and calculated columns without luck. 

(The format of x is from the person I'm doing this for - I thought about changing it to format true/false but I haven't exactly found good reason for it, except for x not being a great data point / format). 

Could you help with this? 

1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

Hi @darrirafn ,

 

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) If you want the slicer to keep three columns when selecting Birds, then we can create a [slicer] table and a [flag] measure.

Slicer = DATATABLE ( 
    "Type", STRING, 
    {
        { "Birds" },
        { "Woods" },
        { "Buildings"}
    }
)
Flag = SWITCH(TRUE(),
ISFILTERED('Slicer'[Type])=FALSE(),1, 
SELECTEDVALUE(Slicer[Type])="Birds" && MAX('Table'[Birds]) <> BLANK(),1,
SELECTEDVALUE(Slicer[Type])="Buildings" && MAX('Table'[Buildings]) <> BLANK(),1,
SELECTEDVALUE(Slicer[Type])="Woods" && MAX('Table'[Woods]) <> BLANK(),1,0)

Use the fields of the [slicer] table as a slicer, and place [flag=1] on the visual object filter.

vtangjiemsft_0-1698805521928.pngvtangjiemsft_1-1698805573063.png

(3)If you want only the Birds column to be displayed on the visual object when the slicer selects Birds, then we can create a field parameter and a measure.

vtangjiemsft_2-1698805669126.png

Flag2 = 
SWITCH(TRUE(),
ISFILTERED(Parameter[Parameter Fields])=FALSE(),1,
SELECTEDVALUE(Parameter[Parameter Order])=0 && MAX('Table'[Birds]) <> BLANK() ,1,
SELECTEDVALUE(Parameter[Parameter Order])=1 && MAX('Table'[Buildings]) <> BLANK(),1,
SELECTEDVALUE(Parameter[Parameter Order])=2 && MAX('Table'[Woods]) <> BLANK(),1,0)

vtangjiemsft_3-1698806113049.png

Best Regards,

Neeko Tang

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

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

Your question is not clear.  Share data in a format that can be pasted in an MS Excel file and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-tangjie-msft
Community Support
Community Support

Hi @darrirafn ,

 

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) If you want the slicer to keep three columns when selecting Birds, then we can create a [slicer] table and a [flag] measure.

Slicer = DATATABLE ( 
    "Type", STRING, 
    {
        { "Birds" },
        { "Woods" },
        { "Buildings"}
    }
)
Flag = SWITCH(TRUE(),
ISFILTERED('Slicer'[Type])=FALSE(),1, 
SELECTEDVALUE(Slicer[Type])="Birds" && MAX('Table'[Birds]) <> BLANK(),1,
SELECTEDVALUE(Slicer[Type])="Buildings" && MAX('Table'[Buildings]) <> BLANK(),1,
SELECTEDVALUE(Slicer[Type])="Woods" && MAX('Table'[Woods]) <> BLANK(),1,0)

Use the fields of the [slicer] table as a slicer, and place [flag=1] on the visual object filter.

vtangjiemsft_0-1698805521928.pngvtangjiemsft_1-1698805573063.png

(3)If you want only the Birds column to be displayed on the visual object when the slicer selects Birds, then we can create a field parameter and a measure.

vtangjiemsft_2-1698805669126.png

Flag2 = 
SWITCH(TRUE(),
ISFILTERED(Parameter[Parameter Fields])=FALSE(),1,
SELECTEDVALUE(Parameter[Parameter Order])=0 && MAX('Table'[Birds]) <> BLANK() ,1,
SELECTEDVALUE(Parameter[Parameter Order])=1 && MAX('Table'[Buildings]) <> BLANK(),1,
SELECTEDVALUE(Parameter[Parameter Order])=2 && MAX('Table'[Woods]) <> BLANK(),1,0)

vtangjiemsft_3-1698806113049.png

Best Regards,

Neeko Tang

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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