Forum Discussion

iGi's avatar
iGi
Advocate I
9 years ago
Solved

Custom Sorting in PowerBI?

Hello all,    Seeing as how helpful people were with my last two questions, I thought I'd ask another, which has been nagging me.    I'm aware of the sorting function in graphs, but that seems to...
Custom Visuals
Dashboards
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    iGi

     

    You can create a table like following one and create relationship with the original table.

     

    Then create a calculated column in original table with following formula.

    Column = RELATED( Table2[ID] )

     

    At last, select the Type column and make it sorted by above created column.

     

     

    Best Regards,

    Herbert

brjones's avatar
brjones
Advocate I
8 years ago

 

Thanks so much for this!  

 

I had to delete some visualizations and re-create them in order for it to work correctly, but it's working beautifully now :)

 

GREAT TIP!

lizaramlee's avatar
lizaramlee
Frequent Visitor
8 years ago

im having same problem, its not sorted out.. do i  need to redo the visualisation?

  • ahxl's avatar
    ahxl
    Regular Visitor
    8 years ago

    Hi guys

     

    This solution worked great for a situation where I had months - August, September and October not displaying in the correct order in my visual.

     

    However, I was wondering, why did my creating of a calculated column in the SAME table using the following formula not work? So within the same table, I created a calculated column:

    COLUMNSORT = IF('Table1'[MONTH] = "August", 1, IF('Table1'[MONTH] = "September", 2, IF('Table'[MONTH] = "October", 3)))

     

    And then I tried to sort MONTH using this new calculated column 'COLUMNSORT', but it displays a message 'This column cannot be sorted by a column that is directly or indirectly sorted by this column'.

     

    Why does it display this error message? I understand the logic that it cant sort the column because this calculated column directly sorts MONTH. So then why can it do a sort off a RELATED function which brings the exact same values into the main table? Isn't it the same thing basically?

     

    Just trying to understand the logic in the two methods.

     

    Thanks all

     

    Regards

    Ahxl

    • djk1000's avatar
      djk1000
      Frequent Visitor
      8 years ago

      ahxl

       

      You can do essentially the same thing, creating your COLUMNSORT column, but do it in the Query Editor with "Add Column", then save and sort by the new column back out  in the PowerBI intrerface. It will get rid of the circular dependency.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi,

       

      Although I have used my sorted column as default column to be used as a sort, Im not able to see my bar chart change the sort automatically.

    • christ7's avatar
      christ7
      Frequent Visitor
      6 years ago

      How did everyone put the ID in the second column in the newly created table?

      What formula is it?