mins, hrs, days measure column not sorting correctly

[AmberJane]

This is a custom measure that is converting a whole number that represents minutes into mins, hrs and days but is not sorting how I would expect. Mins > Hrs > Days or vice versa. 

Any suggestios? 

[Hariharan_R · Accepted solution]

Hi,

You can get the expected order using the reference table.

1. Create one table like below. You can use "Enter Data" option to create this static table.

2. Create one column in your main table in Power query layer. change it for your table column

= Table.AddColumn(#"Changed Type", "Type", each if Text.Contains([TTF], "mins") then "mins" else if Text.Contains([TTF], "days") then "days" else if Text.Contains([TTF], "hrs") then "hrs" else 0)

3. Connect table in step 1 and step 2 using the Type column

4. Create one calculated column in you main table using - 

Order = RELATED('Sort Table'[Order])

5. Select your main column and choose sort by column and select order.

Thanks

Hari