Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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?
Solved! Go to 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.
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.
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.