cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Matisse
Frequent Visitor

Capturing Slicer Selection with SelectedValue

Hi everyone,

 

I have a dashboard with 5 slicers, one for Company, Business Division, Department, Gender and Date. I've created a measure [ReadSlicerSelection] to capture the slicer selection.

 

ReadSlicerSelection = 
var varCompany = SELECTEDVALUE(Table[Company])
var varBusinessDivision = SELECTEDVALUE(Table[Business Division])
var varDepartment = SELECTEDVALUE(Table[Department])
var varGender = SELECTEDVALUE(Table[Gender])
var varStartDate = CALCULATE ( MIN ( Table[Date] ), ALLSELECTED ( Table[Date] ) )
var varEndDate = CALCULATE ( MAX ( Table[Date] ), ALLSELECTED ( Table[Date] ) )
Return

"Company: " & varCompany
& UNICHAR ( 10 ) & "Business Division: " & varBusinessDivision
& UNICHAR ( 10 ) & "Department: " & varDepartment
& UNICHAR ( 10 ) & "Gender: " & varGender
& UNICHAR ( 10 ) & "Start Date: " & varStartDate
& UNICHAR ( 10 ) & "End Date: " & varEndDate

 

The measure works, but I've noticed a weird behaviour. Let's say that there is only one row in the table for the Business Division "Bikes". If I use the "Business division" slicer on the dashboard to select the division "Bikes", Power Bi will not only assign a value to the variable varBusinessDivision, but also automatically assign values to the 5 remaining variables (varCompany, varDepartment, varGender, varStartDate, varEndDate); even though I didn't make a selection with any of those 5 slicers. The only slicer I used was the one for Business Division.

 

I assume that Power BI does this because since there is only 1 row for the division "Bikes", after selecting "Bikes" there's only 1 option each for the remaining variables left.

 

Is there a way to stop this? I want that the variables only get a value assigned if a user makes a selection with the respective slicers (and not auto-completed as it is right now). If the user didn't use a slicer, the variable for that slicer should be BLANK, even if there's only 1 possible option for that variable.

 

I hope you could follow my explanation and that it wasn't too confusing. Any help would be really appreciated 🙂

 

Best,

Matisse

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Matisse , This is strange. Try to use isfiltered or hasonevalue

 

example

 

ReadSlicerSelection = 
var varCompany =  if(isfiltered(Table[Company]), SELECTEDVALUE(Table[Company]), "All") 
var varBusinessDivision = if(isfiltered(Table[Business Division]), SELECTEDVALUE(Table[Business Division]),"All") //Use  "" for empty 
var varDepartment = SELECTEDVALUE(Table[Department])
var varGender = SELECTEDVALUE(Table[Gender])
var varStartDate = CALCULATE ( MIN ( Table[Date] ), ALLSELECTED ( Table[Date] ) )
var varEndDate = CALCULATE ( MAX ( Table[Date] ), ALLSELECTED ( Table[Date] ) )
Return

"Company: " & varCompany
& UNICHAR ( 10 ) & "Business Division: " & varBusinessDivision
& UNICHAR ( 10 ) & "Department: " & varDepartment
& UNICHAR ( 10 ) & "Gender: " & varGender
& UNICHAR ( 10 ) & "Start Date: " & varStartDate
& UNICHAR ( 10 ) & "End Date: " & varEndDate

 

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Matisse , This is strange. Try to use isfiltered or hasonevalue

 

example

 

ReadSlicerSelection = 
var varCompany =  if(isfiltered(Table[Company]), SELECTEDVALUE(Table[Company]), "All") 
var varBusinessDivision = if(isfiltered(Table[Business Division]), SELECTEDVALUE(Table[Business Division]),"All") //Use  "" for empty 
var varDepartment = SELECTEDVALUE(Table[Department])
var varGender = SELECTEDVALUE(Table[Gender])
var varStartDate = CALCULATE ( MIN ( Table[Date] ), ALLSELECTED ( Table[Date] ) )
var varEndDate = CALCULATE ( MAX ( Table[Date] ), ALLSELECTED ( Table[Date] ) )
Return

"Company: " & varCompany
& UNICHAR ( 10 ) & "Business Division: " & varBusinessDivision
& UNICHAR ( 10 ) & "Department: " & varDepartment
& UNICHAR ( 10 ) & "Gender: " & varGender
& UNICHAR ( 10 ) & "Start Date: " & varStartDate
& UNICHAR ( 10 ) & "End Date: " & varEndDate

 

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

It works excellently! Thank you so much, @amitchandak !!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors