Creating slicer for a measure (or suggest a workaround)

[Anonymous]

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.

[amitchandak]

Refer if this can help

https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[Greg_Deckler]

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.

[Anonymous]

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!)

[Greg_Deckler · Accepted 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.