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!
!! Subscribe to my youtube Channel !!

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!
!! Subscribe to my youtube Channel !!

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

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!