Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I tried looking in PBI community but there wasn’t anything there that applied to my situation. Basically, I have raw performance scores (range: 0-5) for employees, and I have a measure that averages all their scores so the average score is also in the range 0-5. I want to create a slicer for the average score, so each employee’s manager can select a range in the slicer, say 1.5-3, and see which employees’ scores fall in that range. But I can’t bring a measure into a slicer – PowerBI limitation. Thanks.
Solved! Go to Solution.
OK, so this is where sample data really really helps. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
So, you may need 2 disconnected tables as I was thinking of your slicer being based on 1, 2, 3, 4, 5. So 2 tables:
Categories
Category
Category 1
Category 2
Category 3
Category 4
Category 5
Scores
Score
1
2
3
4
5
And then a measure perhaps something along the lines of:
Measure =
VAR __Category = SELECTEDVALUE('Categories'[Category])
VAR __Score = SELECTEDVALUE('Scores'[Score])
VAR __Person = MAX('Table'[Person])
VAR __PersonCategoryScore =
SWITCH(__Category,
"Category 1",MAXX(FILTER('Table',[Person] = __Person),[Category 1]),
"Category 2",MAXX(FILTER('Table',[Person] = __Person),[Category 2]),
"Category 3",MAXX(FILTER('Table',[Person] = __Person),[Category 3]),
"Category 4",MAXX(FILTER('Table',[Person] = __Person),[Category 4]),
"Category 5",MAXX(FILTER('Table',[Person] = __Person),[Category 5]),
BLANK()
)
RETURN
IF(__PersonCategoryScore = __Score,TRUE,FALSE)
Warning, no sample data = I wing these DAX calculations without testing so the above code may be rife with syntax issues.
Refer if this can help
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization
Create a disconnected table for your average values. Use that for your slicer. Create a flag measure that uses SELECTEDVALUE to get the value of the slicer and return TRUE for employees that have that value and FALSE for those that do not. Use this measure to filter your other visuals.
Hi Greg, thanks. I am so sorry but I am truly a novice at both DAX and PowerBI. So the disconnected table would look like this, am I correct?
ScoreCategory |
Category1 |
Category2 |
Category3 |
Category4 |
Each employee has several scores (over a time period; scores are created weekly), so the measure in question is the average of all scores. So, for each employee, there is ONE average score for Category1, one average score for Category2, etc.
How would the SELECTEDVALUE look like? And how does this "disconnected table" link to the table that has the average value measures? Thanks. (And sorry!)
OK, so this is where sample data really really helps. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
So, you may need 2 disconnected tables as I was thinking of your slicer being based on 1, 2, 3, 4, 5. So 2 tables:
Categories
Category
Category 1
Category 2
Category 3
Category 4
Category 5
Scores
Score
1
2
3
4
5
And then a measure perhaps something along the lines of:
Measure =
VAR __Category = SELECTEDVALUE('Categories'[Category])
VAR __Score = SELECTEDVALUE('Scores'[Score])
VAR __Person = MAX('Table'[Person])
VAR __PersonCategoryScore =
SWITCH(__Category,
"Category 1",MAXX(FILTER('Table',[Person] = __Person),[Category 1]),
"Category 2",MAXX(FILTER('Table',[Person] = __Person),[Category 2]),
"Category 3",MAXX(FILTER('Table',[Person] = __Person),[Category 3]),
"Category 4",MAXX(FILTER('Table',[Person] = __Person),[Category 4]),
"Category 5",MAXX(FILTER('Table',[Person] = __Person),[Category 5]),
BLANK()
)
RETURN
IF(__PersonCategoryScore = __Score,TRUE,FALSE)
Warning, no sample data = I wing these DAX calculations without testing so the above code may be rife with syntax issues.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |