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

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

Reply
markmsc
Frequent Visitor

Help defining a slicer (some data should be included in all selections)

Here's a situation I can't puzzle out.

 

I have a large table with a column that contain one of three values: A, B, or X.

 

I need to add a slicer to a report that uses this table, based on that column.  However the slicer should only contain two choices: Yes and No. 

 

Yes should include rows with values A or X.

 

No should include rows with values B or X.

 

How can we make a slicer that will do this?  The table is large (hundreds of millions of rows), so solutions with too many steps are probably out as they won't perform.

 

Would love ideas for an elegant solution if anyone has any.  This one is breaking my brain!

 

Thank you.

2 ACCEPTED SOLUTIONS
SamWiseOwl
Super User
Super User

Hi @markmsc 

Use Enter Data to create a table with Two rows for Yes and Two for No join this table onto the large list:

SamWiseOwl_0-1722865790215.png

 

Join these together using Single from the choice table (yes no) to the main table:

SamWiseOwl_1-1722865868999.png

 

Use this in a slicer:

SamWiseOwl_2-1722865932956.png

 

If this works please mark it as a solution for others to find 🙂

 


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

View solution in original post

Gabry
Super User
Super User

Hello @markmsc 

imho you need to work like this

 

Step1 create the table slicer:

SlicerTable =
DATATABLE(
"YesNo", STRING,
{
{"Yes"},
{"No"}
}
)

 

 

Step 2 Create a Measure:

 

IncludeRow =
VAR SelectedValue = SELECTEDVALUE(SlicerTable[YesNo])
RETURN
IF(
SelectedValue = "Yes" && Table[Value] IN {"A", "X"},
1,
IF(
SelectedValue = "No" && Table[Value] IN {"B", "X"},
1,
0
)
)

Step 3 put the measure in the slicer pane:

 

Set the filter to only include rows wher includerow =1

Let me know and don't forget to mark as accepted solution 😉

View solution in original post

3 REPLIES 3
Gabry
Super User
Super User

Hello @markmsc 

imho you need to work like this

 

Step1 create the table slicer:

SlicerTable =
DATATABLE(
"YesNo", STRING,
{
{"Yes"},
{"No"}
}
)

 

 

Step 2 Create a Measure:

 

IncludeRow =
VAR SelectedValue = SELECTEDVALUE(SlicerTable[YesNo])
RETURN
IF(
SelectedValue = "Yes" && Table[Value] IN {"A", "X"},
1,
IF(
SelectedValue = "No" && Table[Value] IN {"B", "X"},
1,
0
)
)

Step 3 put the measure in the slicer pane:

 

Set the filter to only include rows wher includerow =1

Let me know and don't forget to mark as accepted solution 😉

I changed my mind probably you don't need the calculated column

SamWiseOwl
Super User
Super User

Hi @markmsc 

Use Enter Data to create a table with Two rows for Yes and Two for No join this table onto the large list:

SamWiseOwl_0-1722865790215.png

 

Join these together using Single from the choice table (yes no) to the main table:

SamWiseOwl_1-1722865868999.png

 

Use this in a slicer:

SamWiseOwl_2-1722865932956.png

 

If this works please mark it as a solution for others to find 🙂

 


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors