Forum Discussion

rpinxt's avatar
rpinxt
Solution Sage
3 years ago
Solved

Sorting on Parameter fields measures

So did already somebody found a solution to the sorting problem that arises when using the new Parameter Field function? It is really great for switching between Quantity and values but it scr...
Need Help
  • rpinxt's avatar
    rpinxt
    3 years ago

    v-yadongf-msft well yeah of course I can sort it manually but the whole purpose of using Power BI is to make data handling better, easier to use and visually better than manual work 😁.

    Anyway thru several online post I came up with a solution myself for this problem.

    First of you need to make an extera column in the parameter field table:

    It is a copy of the main column in the table but for some reason you cannot use that one.

    (getting error about the composite key etc)

     

    When you have that you kan make a variable Rank Measure like:

    And when you switch to USD view:

    Works like a charm! 😁

     

    And if you don't want to show the rank measure in your visual you can turn word wrap of in column header and the values and then you can just hide the column by sliding the width all to the left making it so narrow that it will nog show anymore.

    For a chart even more easy, you just put it in the tooltips and then you can still sort on it but it won't show in the graph.

craig_morey's avatar
craig_morey
Frequent Visitor
1 year ago

Thanks for posting this, I had the same problem with a slight twist, and you helped me most of the way to a solution.

Just quickly to redescribe the problem, I had a chronological graph that I wanted to swap between showing years, months, weeks etc. PowerBI "Parameters" seemed a perfect fit for use-case, but when you click between periods options, it would lose the sorting of the X-axis (only on some types of periods, not others - which I still don't understand), falling back to sorting by Y-axis, leaving you with a graph that made no sense like this:

so, like you said, I added in an extra column in the parameter:

Then, made a new measure that calculated a rank depending on what the parameter setting was. Note that here the calendar is held in the table "DimCalendar" and the data I'm showing in the graph is in the table "All_Cases":

 

And then I just needed to add that new "RankingPeriod" measure to the tooltips of the chart, and then sort the graph by it (I'm not 100% sure why it only works when sorting DESC, but I'm sure someone smarter will tell me):


and hey-presto, it works as it should:

 

Thanks again... I'm still quite confused why this fairly simple use-case is so tricky in PowerBI, but hey, who knows.