Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 , 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |