Sort legend "High" "Medium" "Low" for multiple stacked bar visuals (based on the same fact table)

[j1s]

I have a Fact table with several columns that contain the text: "High", "Medium", "Low", and some visuals 

Based on the approach explained in this video and elsewhere , I can set the sort order for a single stacked bar visual in a sequence: High, Medium, Low, by creating a new table 'HML Sort Order with two columns, like this:

HML	Sort Order
High	1
Medium	2
Low	3

Then I created a relationship from it's HML column to the first column in the fact table that contains the High, Medium, Low, then using that as the sort order as shown in that helpful video.

However, I need to make another visual chart, from a different column in the Fact table,  using the same sort order as in my HML Sort Order Table. 

But I'm stuck because I can only have one active relationship between my Fact table and the HML Sort Order table, without throwing up an ambiguious paths error,

Surely I don't need to create a separate sort order table for each chart?

[j1s]

I can't use DAX studio in my corporate environment, but thanks anyway, I'll stick to creating extra columns in power query

[Daniel29195 · Accepted solution]

@j1s 

since it is a different column in the fact table, eventhough it seems that i has the same values ( high, low , medium), however i assume it servers another business logic . 

thus it needs another dimtable . 

an alternative would be, to create in the fact,  (using power query ) , a conditional column, stating , that  : 

if high --> 1 , 

medium --> 2 

low --> 3

this way, you can sort all fact tables ( that need to be sorted ) by this new caluclated column

let me know if thi helps. 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[j1s]

Thanks for your reply. I was trying to avoid adding another calculated column to a table with 10s of thousands of rows, (assuming that the most efficient way would be to use a relationship instead?).

Isn't there some way to tell power bi that whenever it sees high medium low it should always put them in that order instead of alphabetical?

[Daniel29195]

@j1s 

well, you can modify the values to : 

1-high

2-medium

3-low

that being said, 

even if you have you add the column order to the facr with 10s of thousands, this would not affect neither performance neither space .

you can check this with dax studio to check the space taken by the order column .

let me know if this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠