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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Memorable Member
Memorable Member

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

5 REPLIES 5
lkalawski
Memorable Member
Memorable Member

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! 😀

AllisonKennedy
Super User
Super User

@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 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, 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.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@lkalawski @AllisonKennedy Is there any other way to use column instead of measure? I want to use column to filter my visual based on it. The reason for doing this, I want to filter my visual on true only. So that if no value was selected, the visual will give blank value.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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