Solved: Re: Displaying Numbers in Words - Page 2

Anonymous · ‎08-07-2020

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?

lbendlin · ‎08-08-2020

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.

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 sd = 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 ")
return hd & dd & sd

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:

View solution in original post

Anonymous · ‎08-10-2020

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.

lbendlin · ‎08-09-2020

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

marcorusso · ‎08-09-2020

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.

lbendlin · ‎08-07-2020

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

Displaying Numbers in Words

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024