Need Advice on Sorting.

[ElvirBotic]

Hello, all, 

Looking for some good advice on creating a sort column in Power BI. I have seen people use excel to create sorts and import, then merge with their existing table and I do not like that approach I get a privacy error since I am using a custom connector. Also, have seen the approach of 'enter data' and I cannot seem to get it to work. 

I have a fact table named "Inspections" then a dimension for "templates" and I want to be able to sort the templates in a specific order that I want and when I have tried do I need to create a 1:1 relationship between the "SortOrder" table and "templates" or do I try to create a 1:* between "Inspections" and "SortOrder"? 

I can clarify if needed.

[v-rzhou-msft · Accepted solution]

Hi @ElvirBotic 

Currently you have a fact table "Inspections" and dimension for "templates". If you want to sort templates by specific order, I think add a sort table or sort column is a good way. 

I build a sample to have a test.

Fact Table:

Dimension Table:

Here I will give some advice.

1. If your number of data in "templates" is not large, you can build a sort column(calculated column) by switch function.

Calcualte column Sort = SWITCH(templates[templates],"A",2,"B",1,"C",3,"D",5,"E",4)

I think you have related fact table and dimension table by "templates" column. If you want to get sort in fact table try related function.

Relate sort from dimension = RELATED(templates[Calcualte column Sort])

Sort template column by sort column in column tool.

2. Your template column has lots of data, get a sort table is a better way.

Use excel(or other data source) to create a sort table and import.

You meet privacy error when you merge the new table with existing table. I think you need to edit the Privacy of two tables to the same in Data source setting. Then you can do merge.

Or you don't need to merge you can relate sort table with dimension table.

Sort templates in sort table by sort column as above.

Then you can use templates in Sort column to build your visuals and your can sort templates by sepcific sort.

If you want to add sort into other tables use related function to build sort column as above.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[parry2k]

@ElvirBotic you have to have a 1:1 relationship between the template table and the sort table or add a sort column directly in the template table.

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡