topic Re: Displaying Numbers in Words in DAX Commands and Tips

Displaying Numbers in Words

Anonymous — Fri, 07 Aug 2020 17:24:12 GMT

Hi,

I'm looking at the way of displaying numbers as words using DAX.

I have a basic sum DAX measure and I want the display to be shown in words so example would be if the total is 100 then I would like it to show " one hundred" if the total adds up to 10.5 then I want it to display "eleven".

Is there a way to display numbers from the measure like that?

Re: Displaying Numbers in Words

lbendlin — Sat, 08 Aug 2020 03:38:59 GMT

in which languages, and which dialects? Do you expect "two thousand one hundred fourty five" or "twenty one forty five" ? etc.

Re: Displaying Numbers in Words

Anonymous — Sat, 08 Aug 2020 06:36:06 GMT

Anonymous

Here's What I tried to implement this using a measure.

Currently this handles upto 9999.

EX = //Currently Handles Upto 9999 //Number to be converted into words. Replace this with SELECTEDVALUE or as desired. VAR Number = SELECTEDVALUE(T[Value])//115 VAR NumberToText = "" & Number VAR NoOfDigits = LEN ( NumberToText ) VAR Ones = SELECTCOLUMNS ( GENERATESERIES ( 0, 9, 1 ), "Ones Digit", [Value], "Ones Digit in Words", SWITCH ( [Value], 1, "One", 2, "Two", 3, "Three", 4, "Four", 5, "Five", 6, "Six", 7, "Seven", 8, "Eight", 9, "Nine" ) ) VAR OneTens = SELECTCOLUMNS ( GENERATESERIES ( 1, 9, 1 ), "One Tens Digit", [Value], "One Tens Words", SWITCH ( [Value], 1, "Eleven", 2, "Twelve", 3, "Thirteen", 4, "Fourteen", 5, "Fifteen", 6, "Sixteen", 7, "Seventeen", 8, "Eighteen", 9, "Nineteen" ) ) VAR Tens = SELECTCOLUMNS ( GENERATESERIES ( 1, 9, 1 ), "Tens Place", [Value], "Tens Words", SWITCH ( [Value], 1, "Ten", 2, "Twenty", 3, "Thirty", 4, "Fourty", 5, "Fifty", 6, "Sixty", 7, "Seventy", 8, "Eighty", 9, "Ninety" ) ) VAR PlaceValue = SELECTCOLUMNS ( GENERATESERIES ( 3, 6, 1 ), "Place", [Value], "Place Value", SWITCH ( [Value], 3, "Hundred", 4, "Thousand") ) VAR X = SELECTCOLUMNS ( GENERATESERIES ( 1, NoOfDigits, 1 ), "Index", [Value], "Dec", VAR N = INT ( Number / INT ( 1 & REPT ( "0", [Value] - 1 ) ) ) RETURN MOD ( N, 10 ) ) VAR numberMod100 = MOD(Number, 100) VAR Y = ADDCOLUMNS ( X, "Text", SWITCH ( TRUE (), [Index] = 1, SWITCH ( TRUE (), numberMod100 > 10 && numberMod100 < 20, MAXX ( FILTER ( OneTens, [One Tens Digit] = [Dec] ), [One Tens Words] ), MAXX ( FILTER ( Ones, [Ones Digit] = [Dec] ), [Ones Digit in Words] ) ), [Index] = 2, SWITCH ( TRUE (), numberMod100 > 10 && numberMod100 < 20, "", MAXX ( FILTER ( Tens, [Tens Place] = [Dec] ), [Tens Words] ) ), // Handles for 1-9 and beyond index 2 IF ( [Dec] = 0, "", // get the Word representation of current Digit MAXX ( FILTER ( Ones, [Ones Digit] = [Dec] ), [Ones Digit in Words] ) // Concatenate the current digit with its place value. & " " & MAXX ( FILTER ( PlaceValue, [Place] = [Index] ), [Place Value] ) ) ) ) RETURN CONCATENATEX(Y, [Text], " ", [Index], DESC)

This can be extended to handle every natural number with few more lines of DAX.

Hope you get the idea.

Hope lbendlin can help on this. I don't know if there's a better way than this.

Thanks

Re: Displaying Numbers in Words

lbendlin — Sat, 08 Aug 2020 13:14:47 GMT

For simplified English you only need to solve for the last group of three digits, in a 1-2 pattern. Any other larger digit places are just repetition, with "thousand", "million" etc slapped on.

At least that's my theory...

Re: Displaying Numbers in Words

lbendlin — Sat, 08 Aug 2020 22:56:59 GMT

Here's my version for the last three digits. "ParameterNumber[ParameterNumber value]" is the measure that you want to convert. Nothing is returned for Zero.

Spelt = var t = "000" & format(ParameterNumber[ParameterNumber Value],"#") var s = right(t,1) var ss = switch(s,"1","one","2","two","3","three","4","four","5","five","6","six","7","seven","8","eight","9","nine","") var sd = right(t,2) var sds = switch(sd,"01","one","02","two","03","three","04","four","05","five","06","six","07","seven","08","eight","09","nine","10","ten" ,"11","eleven","12","twelve","13","thirteen","14","fourteen","15","fifteen","16","sixteen","17","seventeen" ,"18","eighteen","19","nineteen","") var t1 = left(t,len(t)-1) var d = right(t1,1) var ds = switch(d,"2","twen","3","thir","4","four","5","fif","6","six","7","seven","8","eigh","9","nine","") var dd = switch(d,"0","","1","",ds & "ty ") var t2 = left(t1,len(t1)-1) var h = right(t2,1) var hs = switch(h,"1","one","2","two","3","three","4","four","5","five","6","six","7","seven","8","eight","9","nine","") var hd = switch(h,"0","",hs & " hundred ") return hd & dd & if(d<"2",sds,ss)

Re: Displaying Numbers in Words

lbendlin — Sun, 09 Aug 2020 00:28:46 GMT

Here is a more concise version that can also be easily extended to more digit triplets, since the lookup table will be the same for each of the triplets.

Here's the version for up to six digits:

Spelt2 = var l = {("1","one","eleven","ten"),("2","two","twelve","twenty"),("3","three","thirteen","thirty"), ("4","four","fourteen","fourty"),("5","five","fifteen","fifty"),("6","six","sixteen","sixty"), ("7","seven","seventeen","seventy"),("8","eight","eighteen","eighty"),("9","nine","nineteen","ninety")} var t = "0" & format(ParameterNumber[ParameterNumber Value],"#") var s = right(t,1) var d = left(right(t,2),1) var h = left(right(t,3),1) var ss = if(d="1",concatenatex(filter(l,[Value1]=s),[Value3]),concatenatex(filter(l,[Value1]=s),[Value2])) var dd = if(d>"1" || d & s = "10",concatenatex(filter(l,[Value1]=d),[Value4]) & " ") var hd = if(h>"0",concatenatex(filter(l,[Value1]=h),[Value2]) & " hundred ") var s2 = left(right(t,4),1) var d2 = left(right(t,5),1) var h2 = left(right(t,6),1) var ss2 = if(d2="1",concatenatex(filter(l,[Value1]=s2),[Value3]),concatenatex(filter(l,[Value1]=s2),[Value2])) var dd2 = if(d2>"1" || d2 & s2 = "10",concatenatex(filter(l,[Value1]=d2),[Value4]) & " ") var hd2 = if(h2>"0",concatenatex(filter(l,[Value1]=h2),[Value2]) & " hundred ") return if(ss2>"",hd2 & dd2 & ss2 & " thousand ") & hd & dd & ss

In action:

Re: Displaying Numbers in Words

lbendlin — Sun, 09 Aug 2020 17:58:05 GMT

apart from being a nice solution challenge this also brings out one of the uglier sides of DAX. Different from Power Query M (which has the ability to declare functions even if they are not used subsequently) there is no support in DAX for "just in case" functions or application of a measure to parts of a data point.

Let's say you have a seven digit number in you table, and you want to spell it out. Ideally you would convert the number to text, split the text into groups of three, and feed the triplets through the spelling measure, receiving the full text back.

DAX measures don't seem to work that way, they always need the entire value of an existing item to work on. They also do not support recursive calling (as far as I know).

I am not sure if calculation groups may be a possible answer to this but I highly doubt that. marcorusso

Re: Displaying Numbers in Words

marcorusso — Sun, 09 Aug 2020 21:35:04 GMT

You could create a calculation group Conversions with a calculation item ToWords:

VAR OriginalValue = SELECTEDMEASURE () RETURN <your code to convert OriginalValue in words>

Then you apply that conversion to any measure using CALCULATE:

CALCULATE ( [your existing measure], Conversions[Name] = "ToWords" )

I don't like it, but it should work.

The reality is that we need custom functions in DAX.

Re: Displaying Numbers in Words

Anonymous — Mon, 10 Aug 2020 16:55:07 GMT

This actually worked. Took me a while to get my head round what the measure is doing but I actually like it as can use it in other scenarios too.

Thanks a lot for help. Found it to be a good challenge personally.

Re: Displaying Numbers in Words

Anonymous — Thu, 23 Jun 2022 14:19:23 GMT

Hi,

I have been asked to produce something very similar, but also include the decimals as part of the text string. How would I be able to augment the dax measure code below to include the decimal values.

i.e. £1001.99

converts to One Thousand and One Pounds and Ninety Nine Pence

Re: Displaying Numbers in Words

lbendlin — Fri, 24 Jun 2022 02:16:56 GMT

shouldn't that be "One Thousand One Pound and Ninety Nine Pence " ?

You will need to explain the (british) english rules a bit more.

Re: Displaying Numbers in Words

Anonymous — Fri, 24 Jun 2022 08:57:33 GMT

Yes, that is correct typo on my part. I've listed out below a list of examples which follow the convention used in English I think you mean by rules. If the unit value is 1 we use the singular Pound, once pound value is greater than 1 we use the plural (Pounds). Anything to the right of the decimal point is always pence. If I've mis-interpreted your ask please let me know.

Some simple rules:

£0.99 = Ninety Nine Pence

£1.00 = One Pound

£1.99 = One Pound and Ninety Nine Pence

£10.00 = Ten Pounds

£100.00 = One Hundred Pounds

£101.00 = One Hundred and One Pounds
£110.00 = One Hundred and Ten Pounds

£1000.00 = One Thousand Pounds

£1001.00 = One Thousand and One Pounds

£1,010.00 = One Thousand and Ten Pounds

£1,100.00 = One Thousand One Hundred Pounds

£10,000.00 = Ten Thousand Pounds

£10,001.00 = Ten Thousand and One Pounds

£10,010.00 = Ten Thousand and Ten Pounds

£10,100.00 = Ten Thousand One Hundred Pounds

£10,101.00 = Ten Thousand One Hundred and One Pounds

£10,110.00 = Ten Thousand One Hundred and Ten Pounds

£100,000.00 = One Hundred Thousand Pounds

£100,001.00 = One Hundred Thousand and One Pounds

£100,010.00 = One Hundred Thousand and Ten Pounds

£100,100.00 = One Hundred Thousand and One Hundred Pounds

£100,110.00 = One Hundred Thousand One Hundred and Ten Pounds

£101,000.00 = One Hundred and One Thousand Pounds

£101,001.00 = One Hundred and One Thousand and One Pounds

£101,010.00 = One Hundred and One Thousand and Ten Pounds

£101,110.00 = One Hundred and One Thousand One Hundred and Ten Pounds

£110,000.00 = One Hundred and Ten Thousand Pounds

£1,000,000.00 = One Million Pounds

£1,000,001.00 = One Million and One Pounds

£1,000,010.00 = One Million and Ten Pounds

£1,000,100.00 = One Million and One Hundred Pounds

£1,000,110.00 = One Million One Hundred and Ten Pounds

£1,001,110.00 = One Million One Thousand One Hundred and Ten Pounds

£1,010,110.00 = One Million Ten Thousand One Hundred and Ten Pounds

£1,110,110.00 = One Million One Hundred and Ten Thousand One Hundred and Ten Pounds

Regards

JAD22

Re: Displaying Numbers in Words

lbendlin — Sat, 25 Jun 2022 01:01:08 GMT

Sorry but this part

£100,100.00 = One Hundred Thousand and One Hundred Pounds

£100,110.00 = One Hundred Thousand One Hundred and Ten Pounds

makes no sense. Should be either - or. Can we compromise on

£100,100.00 = One Hundred Thousand One Hundred Pounds

£100,110.00 = One Hundred Thousand One Hundred and Ten Pounds

Re: Displaying Numbers in Words

Anonymous — Mon, 27 Jun 2022 08:01:20 GMT

Hi lbendlin,

I accept the compromise statements.

Re: Displaying Numbers in Words

lbendlin — Tue, 28 Jun 2022 01:06:30 GMT

Please try the PBIX I attached with a sample of your expected values, and let me know if the code breaks somewhere.

Re: Displaying Numbers in Words

Anonymous — Tue, 28 Jun 2022 08:55:05 GMT

Hi Ibendlin,

I can't see the .pbx file you attached, can you sent it again so I can test it for you.

Re: Displaying Numbers in Words

lbendlin — Tue, 28 Jun 2022 12:50:36 GMT

Attached to message 13

Re: Displaying Numbers in Words

Anonymous — Wed, 29 Jun 2022 09:25:25 GMT

Hi Ibendlin,

I've done some testing on the Dax, and everything works perfectly. This is a fantastic. It has saved a considerable amount of resource time and effort as well as completely removing the human error factor.
Thank you.

Re: Displaying Numbers in Words

lbendlin — Mon, 11 Jul 2022 23:50:51 GMT

Anonymous thank you for the nice challenge. I learned a lot from it.

Re: Displaying Numbers in Words

Anonymous — Mon, 08 Aug 2022 14:15:03 GMT

Hi Sir,

We have decimals also, we want to convert amount to words along with decimals based on CCY

ex. INR 50.25 = Fifty rupees and twenty five paisa

USD 50.25 = Fifty dollars and twenty five cent

INR 50000.25 = Fifty thousands twenty five paisa

INR 565786.50 = Five lakh sixty five thousand seven hundred eight six fifty paisa

this should be inline with CCY like lakh, crore for INR .. million, billion for USD (as applicable)

Please help me the code.