Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Creating slicer for a measure (or suggest a workaround)

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.

1 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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.