07-23-2017 07:09 AM
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.
For more details and other examples of how you can use this technique, see my blog post on the subject at:
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 =
__BASE_VALUE - __MIN_RATED_VALUE,
__MAX_RATED_VALUE - __MIN_RATED_VALUE
VAR __STAR_RATING = ROUND(__NORMALIZED_BASE_VALUE * __MAX_NUMBER_OF_STARS, 0)
& REPT(UNICHAR(9734), __MAX_NUMBER_OF_STARS - __STAR_RATING)
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]
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:
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