Star Ratings 01-06-2022 08:03 AM

[cwebb]

This quick measure displays a score or rating out of five as a series of filled and unfilled stars. In the example in the report, there is a table called restaurants and a numeric column on that table called Score that contains a rating out of five. This measure takes the value in the Score column and displays the same number of filled stars (using the DAX Unichar() function) followed by whatever number of unfilled stars are needed to make the total number of stars up to five.

DAX:

Stars =

REPT(UNICHAR(9733), AVERAGE('Restaurants'[Score]))

&

REPT(UNICHAR(9734), 5-AVERAGE('Restaurants'[Score]))

For more details and other examples of how you can use this technique, see my blog post on the subject at:

https://blog.crossjoin.co.uk/2017/04/11/the-dax-unichar-function-and-how-to-use-it-in-measures-for-d...

eyJrIjoiYzYzZmFkMDEtZmFhZi00MzJlLWE2NGEtZGE3ZjFiOTRiYzRmIiwidCI6ImEyODQ0OTIzLTEyMTgtNGQzNi1hN2M1LTk1ODJhMzhhNGU1ZCIsImMiOjh9

[levialae]

@cwebb Hi Chris

Thanks for the tool!

I have a question: Is it possible to change the formula so that you get a lower star-ranking the higher the value you score. I.e. number of defects should be as low as possible and generate more stars instead of fewer.

[bideveloper555]

hi

This is working great but using slicer it doesnt work.

i am trying to slice based on some user names but rating doesnt work.

 i am slicing on column"Name" 

Any help please.

Stars =

REPT(UNICHAR(9733), CALCULATE(AVERAGE('PowerBI vwtable'[starcolumn]),ALLEXCEPT('usertable',usertable[Name])))

&

REPT(UNICHAR(9734), CALCULATE(5-AVERAGE('PowerBI vwtable'[starcolumn]),ALLEXCEPT('usertable',usertable[Name])))

Many thanks in Advance

[skorpionaa]

can we chnage the Colours of stars ?

[GhaziSalahuddin]

Hi @skorpionaa 

Yes, you can change the color. Below are the steps:

1 - Click on the format menu.

2 - Click on the Field Formatting option.

3 - From the dropdown, select the rating option (or maybe the one which you have chosen while creating the rating measure).

4 - Click on Font color and choose your favorite color.

Happy Learning!

[justindoh]

@skorpionaa 

Were you able to find a solution (colored star)?

Thanks.

[GhaziSalahuddin]

Hi @justindoh 

Yes, you can change the color. Below are the steps:

1 - Click on the format menu.

2 - Click on the Field Formatting option.

3 - From the dropdown, select the rating option (or maybe the one which you have chosen while creating the rating measure).

4 - Click on Font color and choose your favorite color.

Happy Learning!

[Anonymous]

Check out this short Youtube video as well on how to create quick measure:

Youtube video: Power BI Quick Measures

[Anonymous]

Hi chris, if the avg rating is 4.5 or 3.5 will it show half filled stars? or how can we do it.

[cwebb]

As far as I can see there's isn't a unicode character for a half-filled star, so it would not be possible to use this technique to display half-filled stars.

Sorry!

Chris

[dcresp]

@cwebb 

This seems to provide a half star. Is it possible to use this for a half star rating? https://www.fileformat.info/info/unicode/char/2bea/index.htm

[safadig]

A solution would be to:

1. Create an image for each star rating you liked displayed ( including half ratings).

2. Upload image to a URL.

3. Create calculated column in data based on rating.

4. Add Column by example that include Properly named image URL column to reflect your rating image.

5. Change Poperties of URL Column under Modeling to image url.

6. Use you new URL image column in the table to show the ratings

[mdaamirkhan]

Hello Everyone,

I have created quick measure "Star Rating" based on sales value but I dont the Value for highest star rating. Sales value either be Million or Billion or less.

How I want to set MAX_RATED_VALUE dynamic based on sales? Can you please help me on this ?

Sales_LCD star rating =
VAR __MAX_NUMBER_OF_STARS = 5
VAR __MIN_RATED_VALUE = 0
VAR __MAX_RATED_VALUE = 1000000
VAR __BASE_VALUE = SUM('Pfizer Champix QTR'[Sales_LCD])
VAR __NORMALIZED_BASE_VALUE =
MIN(
MAX(
DIVIDE(
__BASE_VALUE - __MIN_RATED_VALUE,
__MAX_RATED_VALUE - __MIN_RATED_VALUE
),
0
),
1
)
VAR __STAR_RATING = ROUND(__NORMALIZED_BASE_VALUE * __MAX_NUMBER_OF_STARS, 0)
RETURN
IF(
NOT ISBLANK(__BASE_VALUE),
REPT(UNICHAR(9733), __STAR_RATING)
& REPT(UNICHAR(9734), __MAX_NUMBER_OF_STARS - __STAR_RATING)
)

[cwebb]

You need to create another measure in your report that returns the max value, and then replace the hardcoded max value (1000000 in your example) with the name of the measure.

So, for example, if you created a measure called [Max Sales] that returned the maximum sales visible on your report (this might be quite difficult to do though...) then the highlighted line would be:

VAR __MAX_RATED_VALUE = [Max Sales]

Regards,

Chris

[mdaamirkhan]

I have a measure called [Max Sales]  

Query is ok or not

MaxSales = MAX('Pfizer Champix QTR'[Sales_LCD])

[mdaamirkhan]

I already tried that its showing 5-stars in all sales but where sales vlue is 0 it showing not stars.

[cwebb]

The expression MAX('Pfizer Champix QTR'[Sales_LCD]) will give you the maximum value found in a row in the 'Pfizer Champix QTR' table in your data model, which is not necessarily the maximum value displayed in your visualisation. If you add it to your table as a separate column, does it give you the values you expect? My guess is that you'll need to use the MAXX() function in a more complex measure, and not the MAX() function here:

https://msdn.microsoft.com/en-us/library/ee634576.aspx

Chris

[mdaamirkhan]

i found that solution and thanks for helping me