Forum Discussion
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?
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.
2 Replies
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.
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.
