Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Displaying Numbers in Words

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 wou...
  • lbendlin's avatar
    lbendlin
    6 years ago

    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:

     

     

lbendlin's avatar
lbendlin
Super User
6 years ago

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's avatar
marcorusso
Most Valuable Professional
6 years ago

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.