Filter visual based on value not being blank

[darrirafn]

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? 

[Ashish_Mathur]

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.

[v-tangjie-msft · Accepted solution]

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.

(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.

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)

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.