Setting different data types for measures used in SWITCH function

[Smile]

Hi,

I have two values in my SWITCH function as below:

SWITCH (

        SELECTEDVALUE('ColumnA')

        ,1, CURRENCY('Sales') -- and added $ in front of the displayed currency by using the $ sign button under Format in the top ribbon

        ,2, FORMAT('UnitCount', "#,##0")

)

With the above, I could display 'Sales' in the format, $1,234 whereas 'UnitCount' in the format, 1,234. -> (1)

However, I quickly realised that with this, I am not able to sort 'UnitCount' by values as is in the data type, Text.

When I tried switching 'UnitCount' to VALUE('UnitCount'), it added $ sign in front of the number, which is not the desired outcome.

Does anyone have recommendation as to how I can still show the values in the necessary format (1) and allow users to sort by their numberical values for both 'Sales' and 'UnitCount'?

Thank you!

[Smile]

Thank you so much for your help here @djurecic !

[djurecic · Accepted solution]

Hi @Smile ,

 There is not a way to do this with a SWITCH statement. Microsoft recommends trying calculation groups.

You can vote for the idea here:

https://ideas.powerbi.com/ideas/idea/?ideaid=09c2a6d2-d215-4d68-b500-21167d177461

Calculation groups:

https://www.sqlbi.com/articles/controlling-format-strings-in-calculation-groups/