Capturing Slicer Selection with SelectedValue

[Anonymous]

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

[amitchandak · Accepted solution]

@Anonymous , 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.

[Anonymous]

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