cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Trying to add a column that displays true/false depending on slicer selection.

I have a relatively simple problem and haven't gotten a working solution. As the title says I'm trying to create a table with a slicer attached that will give me "True" if the current product is selected or "False" if it is not. This is so I can make a "this or everything else" type of stacked bar chart, and I can't get it to work as expected. Example:

Slicer:
( ) Product 1
( ) Product 2
(x) Product 3
Product 4

 

Product:Result:
Product 1false
Product 2false
Product 3true
Product 4false


And obviously the table(visualization) itself is not affected by the slicer so it will always display all products.

My first approach was using a calculated column like 

if(SELECTEDVALUE('table'[Product]) = 'table'[Product], "true","false") But the SELECTEDVALUE function returns blank no matter what. I also tried
if (FIRSTNONBLANK(('table'[Product]),1)) = 'table'[Product], "true","false"
along with some variation of  CALCULATE and ALLSELECTED on the previous formula, but no matter what I do to it, when it's in a visual that is not affected by the slicer, FIRSTNONBLANK always reads the product value for the current row, and so it always returns true. Any help would be greatly appreciated, because it just feels like this is not working the way that it should.
1 ACCEPTED SOLUTION
lkalawski
Super User II
Super User II

Hi @Anonymous 

You should create the additional table with the list of products (only the one column) and then create a measure:

 

IsSelected = 
VAR __Product = SELECTEDVALUE('Prod'[Column1])
RETURN
IF(__PRODUCT in VALUES('Table'[Product]) , TRUE(), FALSE())

 

Prod[Column1] - from new table. 

lkalawski_0-1606339071529.png

 



_______________
If I helped, please accept the solution and give kudos! 😀

View solution in original post

4 REPLIES 4
lkalawski
Super User II
Super User II

Hi @Anonymous 

You should create the additional table with the list of products (only the one column) and then create a measure:

 

IsSelected = 
VAR __Product = SELECTEDVALUE('Prod'[Column1])
RETURN
IF(__PRODUCT in VALUES('Table'[Product]) , TRUE(), FALSE())

 

Prod[Column1] - from new table. 

lkalawski_0-1606339071529.png

 



_______________
If I helped, please accept the solution and give kudos! 😀

View solution in original post

AllisonKennedy
Super User III
Super User III

@Anonymous See my post here on Power BI order of operations, you will need to use a MEASURE not a COLUMN if you want slicer selection to updat the value: Power BI Order of Operations 



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Anonymous
Not applicable

I tried doing that, but then I was unable to make a true/false calculated column that compares the product in the current row to the value of that Measure.

@Anonymous You cannot do a calculated column, you will need to put the calculated MEASURE in the Values of a Matrix or Table visualization. This can only be done in the report view as a visualization. Slicers cannot update the data model. Try using a measure similar to the one @lkalawski has suggested, though since your Product slicer is not impacting the visual, you may not need the added step of creating a new table. Please share your relationship view with table and column names if you want help specific to your tables.



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.