Sort table by multiple columns in a specific order (NOT ascending/descending)

How to sort a table by two columns simultaneously in a specific order (NOT ascending/descending).
i.e. 5 phases for a project with 20+ milestones in each phase.
I did assign index to phases and it worked.
I also assigned index to Milestones which works only when i filter by one phase.
I need it to work without applying filter.

Hope it makes sense.

D&W Dashboard by irina.pbix (614.4 KB) DW Projects Control and Assurance Plan.xlsx (202.9 KB)

Thank you in advance. irina.

Hi Irina,

Welcome to the Forum!

I added a combination of the Phase and Milestone index, you can now use that as a custom sort order.

If you change the added FileLocation parameter in the Query Editor, all queries will be restored.
Here’s your file. I hope this is helpful. D&W Dashboard by irina.pbix (614.4 KB)

1 Like

Hi Melissa.
Thank you for taking time to look at my question. I like your solution!

I have changed the FileLocation parameter and queries were restored. But i couldn’t see the Index column as in the attached screenshot 1-01…2-01 in your email. I see it as in my old file - 1,2…19

I would like to know how do I do it my self.
Did you do DAX?
Or, did you do it through AddColumn -> Custom Colomn -> Table.AddIndexColumn([Data], “Index”, 0, 1)?

Would be helpful to learn how to do it. I have other scenarios where i have to use this technique.

Thanks a lot. Appreciate it.


My bad, it looks like I returned the wrong file… :upside_down_face:

Anyway I extended your Milestones Query as follows:

  1. Merged the Phases, to lookup the Phase key
  2. Added a custom column for the new Index where I’ve combined the Phase & Milestone Indexes
  3. Cleaned up the columns

Here’s the right file. :crossed_fingers:
D&W Dashboard by irina.pbix (623.8 KB)

1 Like

Thank you very much Melissa. This is awesome.

I understood the solution - it worked :smiley:

Very grateful. i.