When I was working on the What-If contest, one of the requirements that I wanted to add was text based details. But the kicker was that when I put the text values into a measure it was showing the values, but based on an alphabetical order instead of being based on the numerical values.

One challenge for me was to not only return the data in the correct order, but to also return the Text based data, as I could easily do it with standard measures.

As with most of my blog posts it is best shown with an example.

  • Below you can see that I used the default Concatenate, using the TOPN 3, and as shown below the order of the crimes go from Fare Evasion, Weapons and Miscellaneous Offences
  • But what I wanted was the text to show it in the order of the Visual above, which should be “Fare Evasion, Weapons, Miscellaneous Offences, etc”

Creating the Text Based Measure to show data in correct order

In the syntax below I am going to show you how I achieved this and I am going to break it down into sections, which will make it easier to explain before having the entire measure at the end.

Variables for ThirdName in List

Below I am going to show you what I am doing when I created the ThirdName Variable

NOTE: The reason that I am showing this example with the ThirdName is because when it is for the FirstName, there is only 1 name so it makes it a bit more difficult to explain.

  • Here is the syntax
    VAR ThirdName =
    TOPN (
        3,
        VALUES ( 'QLD Police Crimes'[Crime Category] ),
        [% Year Change], DESC
    )

     

  • What I am doing here is a fairly standard way of getting the TOP 3 values for the Crime Category based on my measure [% Year Change]
  • And then sorted this Descending.
  • If you had to see this returned in a table it looks like the following.
  • Now the order is very important, which I will explain with my next variable that I created in my measure.

Variable for ThirdNameLabel

Below I am going to show how I am using the previous variable in this variable to get the value I am looking for, where what I want to get is the last value in the table above which is “Miscellaneous Offences”

  • Here is the Syntax
    VAR ThirdNameLabel =
    TOPN ( 1, ThirdName, [% Year Change], ASC )

     

  • What I am doing with this variable is I am once again using the TOPN function but this time I am only returning the TOP 1 value from my previous variable called “ThirdName”
  • And then I am ordering it Ascending this time.
  • So that my result set will only return the last row “Miscellaneous Offences”
  • If you had to see this returned in a table it looks like the following.
  • And this is the value that I want to be returned for the 3rd value in my dataset.
  • As well as this then matches my chart shown previously where the 3rd value was “Miscellaneous Offences”

Variables for SecondName and SecondNameLabel

For the Second Name and Second Name Label I did something every similar with the Variables, but instead of using the TOP 3 I used the TOP 2. And this is shown below, without the detailed explanation as this has been explained above

  • Here is the Syntax
    • VAR SecondName =
    TOPN (
        2,
        VALUES ( 'QLD Police Crimes'[Crime Category] ),
        [% Year Change], DESC
    )

       

  • And this is what it would look like in a table
  • And here is the second measure
    • VAR SecondNameLabel =
    TOPN ( 1, SecondName, [% Year Change], ASC )

       

  • And this is what it would look like in a table

Final output for Text Based Measure

This is what it finally looked like after putting it all together and in my report

And if I had to change the What-If year to another value it would dynamically change

Conclusion

As I have demonstrated, I can now show my text based data showing in the correct order of my values.

If you want to see a working example you can view it here in the Data Stories Gallery: Power BI – Queensland Crimes What-If

If there are any questions or comments please leave them in the section below.

Entire Measure

Below is the entire measure that I created, and I put this at the end as it quite long.

Top 3 Crimes List = 
VAR FirstName =
    TOPN (
        1,
        VALUES ( 'QLD Police Crimes'[Crime Category] ),
        [% Year Change], DESC
    )
VAR FirstNameLabel =
    TOPN ( 1, FirstName, [% Year Change], ASC )
VAR FirstPercentChange =
    FORMAT (
        CALCULATE (
            [% Year Change],
            'QLD Police Crimes'[Crime Category] = FirstNameLabel
        ),
        "Percent"
    )
VAR SecondName =
    TOPN (
        2,
        VALUES ( 'QLD Police Crimes'[Crime Category] ),
        [% Year Change], DESC
    )
VAR SecondNameLabel =
    TOPN ( 1, SecondName, [% Year Change], ASC )
VAR SecondPercentChange =
    FORMAT (
        CALCULATE (
            [% Year Change],
            'QLD Police Crimes'[Crime Category] = SecondNameLabel
        ),
        "Percent"
    )
VAR ThirdName =
    TOPN (
        3,
        VALUES ( 'QLD Police Crimes'[Crime Category] ),
        [% Year Change], DESC
    )
VAR ThirdNameLabel =
    TOPN ( 1, ThirdName, [% Year Change], ASC )
VAR ThirdPercentChange =
    FORMAT (
        CALCULATE (
            [% Year Change],
            'QLD Police Crimes'[Crime Category] = ThirdNameLabel
        ),
        "Percent"
    )
RETURN
  "Crime with the Largest Change for the past 
  " & SELECTEDVALUE('Years Back Comparison'[Years Back Comparison]) 
  & " years is: 
  """ & FirstNameLabel & " - " & FirstPercentChange & """ , 
  """ & SecondNameLabel & " - " & SecondPercentChange & """, 
  and 3rd largest change is : 
  """ & ThirdNameLabel & " - " & ThirdPercentChange &""""