cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
QuangMC
Frequent Visitor

Can you switch measure results dynamically

Hello all,

 

So i've had this question for a while now, but never got to find a correct way of doing this.

Context :

I have an "Evaluations" table, that has test results from multiple establishments.

I have to return the average results for each establishments.

The results are on a scale from 0 to 20.

Pretty easy so far.

 

Problem :

In a matrix visual, I need to show the average for each establishment, not as number, but as a letter.

For example, if the average of an establishment is 11 out 20, I need to show "C+". If the average is 17 out of 20, I need to show "A". And so on.

 

For now, I have had to do it all manually with multiple IF functions. It works, but this seems like a terrible solution, espacially if I have to had more IF later...

Isn't there any way to do this dynamically

 

So far, I have tried with less values, by first building a correspondance table like the following :

Possible_ResultsWhat_I_Want_To_See
1A
2B
3C

4

D

5

E

 

Then, I tried doing something like this in a measure :
If(

AVERAGE([Establishment_Result]) in [Possible_Results]

,[What_I_Want_To_See]

)

 

I also tried with SWITCH(), but it doesn't work. I haven't found any threads with similar problems, and I really can't think of anything alse I can try right now. Any clues would be appreciated !

 

Thank you in advance !

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@QuangMC ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

 

To me seem like a case of bucketing/ Dynamic segmentation

 

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...


Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@QuangMC ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

 

To me seem like a case of bucketing/ Dynamic segmentation

 

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...


Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

@amitchandak sorry, I just found my answer ^^'

 

I "just" needed to use a LOOKUPVALUE within my measure...

 

I tried to use the LOOKUPVALUE function before, but the results weren't what i expected. Turns out, it actually worked as intended, but my data quality was awful, and it ended in bad results.

 

This is actually pretty easy, I just ruled it out because I though it didn't work...

 

Sorry to have wasted your time ^^'

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors