Forum Discussion
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
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: " & varEndDateCan you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
2 Replies
- amitchandak
Super User
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: " & varEndDateCan you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
It works excellently! Thank you so much, amitchandak !!