Sort by (in Switch)

[Anonymous]

Hello, 

I have a series of value ranges.   I know I could enforce the proper sorting be prefacing each one by using something like 1. xxxx   2. xxxx   or a. xxxxx   b. xxxxx, which I don't want to do for aesthetic reasons.  

Is there a way, within this formula, to enforce the same sort order in which they are written below?    So, when I put them in a chart visual for example, they appear in the legend in same order as below.    Or a better solution?

Value Cohorts = SWITCH

    (TRUE(),

    'AllOpps-Products'[Extended Price]<100000,"$0 - $100k",

    'AllOpps-Products'[Extended Price]<500000,"$100k - $499k",

'AllOpps-Products'[Extended Price]<1000000,"$500k - $999k",

    'AllOpps-Products'[Extended Price]<5000000,"$1M - $4.9M",

    'AllOpps-Products'[Extended Price]<10000000,"$5M - $9.9M",

    'AllOpps-Products'[Extended Price]>=10000000,"$10M +",BLANK()

    )

[TomMartens]

Hey @Anonymous ,

it's not possible to define a sort order from within a DAX statement.

This article describes how a column can be used by another column: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column#select-which-column-to-use-for-sorting

Regards,

Tom

[parry2k · Accepted solution]

@Anonymous add another coloum for sort

Value Cohorts Sort = SWITCH
    (TRUE(),
    'AllOpps-Products'[Extended Price]<100000,1,
    'AllOpps-Products'[Extended Price]<500000,2,
'AllOpps-Products'[Extended Price]<1000000,3,
    'AllOpps-Products'[Extended Price]<5000000,4,
    'AllOpps-Products'[Extended Price]<10000000,5,
    'AllOpps-Products'[Extended Price]>=10000000,6
    )

and now select Value Cohorts column, in column tool, sort it by Value Cohort Sort

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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.⚡