topic Format measure to showing ordinary numbers in DAX Commands and Tips

Format measure to showing ordinary numbers

lucie_rabochova — Wed, 14 Aug 2024 11:03:04 GMT

Hello,

I have very simple measure contains SUM of numbers. The result of this measure is standard number e.g. 1 2,3,4,....59,..... etc.

It is possible to recreate this measure to showing ordinary numbers like 1st, 2nd....21th etc?

Thank you for your help

Re: Format measure to showing ordinary numbers

SachinNandanwar — Wed, 14 Aug 2024 11:19:48 GMT

If you mean ordinal numbers then unfortunately there isnt an inbuilt function in DAX.However you can have a look at this article on possible ways on how to achieve it

https://gorilla.bi/power-bi/ordinal-numbers/

Re: Format measure to showing ordinary numbers

Anonymous — Thu, 15 Aug 2024 06:13:58 GMT

Hi lucie_rabochova

Based on your needs, I have created the following table.

Then you can use the following Dax to get the result you want:

Ordinal Numbers = VAR _Last2Number = RIGHT( [Standard number], 2 ) VAR _LastNumber = RIGHT( [Standard number], 1 ) RETURN [Standard number]&SWITCH( TRUE( ), _Last2Number IN { "11", "12", "13" }, "th", _LastNumber = "1", "st", _LastNumber = "2", "nd", _LastNumber = "3", "rd", "th" )

Result:

Best Regards,

Jayleny

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

Re: Format measure to showing ordinary numbers

lucie_raboch — Thu, 15 Aug 2024 06:38:31 GMT

Thanks a lot, it works perfect!