Forum Discussion
Displaying Numbers in Words
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 & sdHere'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 & ssIn action:
Super User
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)
Super UserHere 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
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
Most Valuable Professional
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.
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.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.
- lbendlin
Anonymous please provide more samples, especially with crore and lakh sized values. Please indicate if "and" is required before the paisa.
Attached is aproposal to handle the Indian number format. Please validate.
Yes "and" is required before paisa.
Please find the below samples.
5,75,786.56 -- Five lakh seventy five thousand seven hundred eighty six Rupees and fifty-six Paisa
89,75,88,789.25 --- Eighty Nine core seventy five lakhs eighty eight thousand seven hundred eighty nine Rupees and twenty five paisa.
I will validate the PBIX and confirm you sir.
