This post will answer how to sort a measure that returns text values to a custom order, without affecting other columns. It will utilize the DAX functions of REPT() and UNICHAR(8203) – a Zero width space.
The requirements
I’ve been working at a florist! In this example, I have been in charge of looking after four plants, named A, B, C and D. The florist owner is a big Power BI fan, and asked me to measure how much water I have been giving them a day to put in a report. They need at least 20ml to survive, but over 50ml will stop them growing as well.
Create a table with the flowing:
The flowers get under 20 ml, label as Bad.
When the flowers get 20 – 50 ml, label as Good.
Finally, if the flowers receive over 50 ml, label as Warning.
I’ve been asked to show them in order of Bad, Warning then Good. This is vital so the plants needing attention are at the top of the table.
Creating the table
Here is the measure I create:
Adding this to a table:
Now comes the question, how can I order this to put Bad and Warning together? If I order by Water Target measure, this will be alphabetical. Sorting by WaterIntake can not give me the correct order either.
One option would be to make a conditional column and use the “Sort by Column”. However, this may be a complicated calculation, especially on more complex measures. In addition it will sort every visual by this column, when I only want to sort in this one table.
Creating the custom sort
My solution? Make use of the UNICHAR() function. For those unaware of this function, UNICHAR() can return characters based on their UNICODE number. This can include more text characters not included on the standard keyboard.
A character that can help is UNICHAR(8203). This is a “Zero width space”. This is a space that has not width, so it is essentially invisible and will not be visible in the measure. The Zero width space is still recognized as a character by DAX. Spaces come before any letter in the alphabet. Two spaces comes before one, and so on.
The second function I will utilize is REPT(). REPT() or replicate, simply repeats text multiple times. It takes two arguments, the text and the times to repeat.
For example: REPT( "Hi", 3 ) will return the text "HiHiHi"
To change the sort order, I will repeat the Zero width space in front of the text. The text I want to appear first will have the space repeated the most amount of times. This will put it first in an alphabetical list. I will use the & symbol to concatenate the Zero width spaces and the text.
Now, “Bad” has the Zero width space repeated three times in front of it. This now puts it first in an alphabetical list. Warning has the Zero width space repeated twice, putting it second. “Good” has it once putting it third.
Applying the sort
Now I can arrange my table by Water Target (alphabetical), in an ascending order:
And success! I’ve added a custom sort to my text measure, without making any other measures or columns.
If you like the content from PowerBI.Tips please follow us on all the social outlets to stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.
Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
Zero-width space FTW! I had no idea about this – thanks, Mark!
Awesome pattern! Never used zero-width space, but that’s so clean.
One additional best practice to consider: Create a variable for the value that you’re comparing in the SWITCH(). That way you don’t have to evaluate it multiple times. If this were a complex measure, it would cause performance to drop. Try this instead:
Water Target =
VAR Intake_Actual =
SUM(‘Table'[WaterIntake])
VAR Result =
SWITCH( TRUE()
,Intake_Actual 50, REPT(UNICHAR(8203), 2) & “Warning”
,REPT(UNICHAR(8203), 1) & “Good”
)
RETURN
Result
Thanks for the input! This is for sure best practice.
I didn’t want to complicated the DAX code in the post when I wrote this so tried to keep it simple.
Anyone who is interested in learning more about variables (Hint: Yous should be!), check here:
https://powerbi.tips/2017/05/using-variables-within-dax/
Great suggestion, thank you!
Love this…coming from Qlikview you could have a value contain both a number and text so you could do custom sorts more natively, but this works pretty much the same. Thanks for the tip!
Great info! How could I apply this if it is textually based? For example if text is ABC I want 4 zero based spaces? Since this version is using “Sum” I’m looking for the synonym. I do not have access to the data model so can’t use the approach of adding a calculated column.
The switch statement is applying rules on SUM in this example, but it can be applied on your choice.
the portion:
REPT( UNICHAR(8203) , 1 ), the last argument (1 in this case) defines the amount of spaces
You can use TRUE() in a switch and declare the text. For example:
Measure =
SWITCH( TRUE(),
“ABC”, REPT( UNICHAR(8203) , 4 ) & “ABC”,
“ZBA”, REPT( UNICHAR(8203) , 3 ) & “ZBA”
)
Would label “ABC” as with 4 zero width space, “ZBA” with 3
Thank you. I was also looking for this solution. Superb.
This was so helpful to me! I was struggling to sort the rows in a matrix without the tooltip option and this was the answer. Thanks!
Literally the most helpful thing on the internet (today)
Thank you!