ROMAN

Greg_Deckler — Fri, 24 Jul 2020 21:09:26 GMT

OK, I have held off posting this as part of my series on Excel to DAX Translation. The reason is that apparently the DAX code I used exposed a bug nested deep within the bowels of DAX. You can read about the issue here. The first link is to the original discussion between marcorusso and I. marcorusso thought that it would be a good idea to not post the DAX code as it could have potentially been used to launch a DOS attack on the Power BI Service.

The code posted here took 6 hours or so to complete on my desktop originally. Now as of the July update it executes within seconds. That is one AMAZING job done by the fine Microsoft folks who are responsible for the DAX language!!

ROMAN = VAR __Value = MAX('Arabic'[Value]) VAR __Classic = DATATABLE( "Arabic",INTEGER, "Roman",STRING, { { 1, "I" }, { 4, "IV" }, { 5, "V" }, { 9, "IX" }, { 10, "X" }, { 40, "XL" }, { 50, "L" }, { 90, "XC" }, { 100, "C" }, { 400, "CD" }, { 500, "D" }, { 900, "CM" }, { 1000, "M" } } ) VAR __Concise1 = DATATABLE( "Arabic",INTEGER, "Roman",STRING, { { 1, "I" }, { 4, "IV" }, { 5, "V" }, { 9, "IX" }, { 10, "X" }, { 40, "XL" }, { 45, "VL" }, { 50, "L" }, { 90, "XC" }, { 95, "VC" }, { 100, "C" }, { 400, "CD" }, { 450, "LD" }, { 500, "D" }, { 900, "CM" }, { 1000, "M" } } ) VAR __Convert = __Classic // 1st digit VAR __1 = __Value VAR __1Table = ADDCOLUMNS(__Convert,"Diff",__1 - [Arabic]) VAR __1Min = MINX(FILTER(__1Table,[Diff] >= 0),[Diff]) VAR __1R = MAXX(FILTER(__1Table,[Diff] = __1Min),[Roman]) VAR __1A = MAXX(FILTER(__1Table,[Diff] = __1Min),[Arabic]) // 2nd digit VAR __2 = __Value - __1A VAR __2Table = ADDCOLUMNS(__Convert,"Diff",__2 - [Arabic]) VAR __2Min = MINX(FILTER(__2Table,[Diff] >= 0),[Diff]) VAR __2R = MAXX(FILTER(__2Table,[Diff] = __2Min),[Roman]) VAR __2A = MAXX(FILTER(__2Table,[Diff] = __2Min),[Arabic]) // 3rd digit VAR __3 = __Value - __1A - __2A VAR __3Table = ADDCOLUMNS(__Convert,"Diff",__3 - [Arabic]) VAR __3Min = MINX(FILTER(__3Table,[Diff] >= 0),[Diff]) VAR __3R = MAXX(FILTER(__3Table,[Diff] = __3Min),[Roman]) VAR __3A = MAXX(FILTER(__3Table,[Diff] = __3Min),[Arabic]) // 4th digit VAR __4 = __Value - __1A - __2A - __3A VAR __4Table = ADDCOLUMNS(__Convert,"Diff",__4 - [Arabic]) VAR __4Min = MINX(FILTER(__4Table,[Diff] >= 0),[Diff]) VAR __4R = MAXX(FILTER(__4Table,[Diff] = __4Min),[Roman]) VAR __4A = MAXX(FILTER(__4Table,[Diff] = __4Min),[Arabic]) // 5th digit VAR __5 = __Value - __1A - __2A - __3A - __4A VAR __5Table = ADDCOLUMNS(__Convert,"Diff",__5 - [Arabic]) VAR __5Min = MINX(FILTER(__5Table,[Diff] >= 0),[Diff]) VAR __5R = MAXX(FILTER(__5Table,[Diff] = __5Min),[Roman]) VAR __5A = MAXX(FILTER(__5Table,[Diff] = __5Min),[Arabic]) // 6th digit VAR __6 = __Value - __1A - __2A - __3A - __4A - __5A VAR __6Table = ADDCOLUMNS(__Convert,"Diff",__6 - [Arabic]) VAR __6Min = MINX(FILTER(__6Table,[Diff] >= 0),[Diff]) VAR __6R = MAXX(FILTER(__6Table,[Diff] = __6Min),[Roman]) VAR __6A = MAXX(FILTER(__6Table,[Diff] = __6Min),[Arabic]) // 7th digit VAR __7 = __Value - __1A - __2A - __3A - __4A - __5A - __6A VAR __7Table = ADDCOLUMNS(__Convert,"Diff",__7 - [Arabic]) VAR __7Min = MINX(FILTER(__7Table,[Diff] >= 0),[Diff]) VAR __7R = MAXX(FILTER(__7Table,[Diff] = __7Min),[Roman]) VAR __7A = MAXX(FILTER(__7Table,[Diff] = __7Min),[Arabic]) // 8th digit VAR __8 = __Value - __1A - __2A - __3A - __4A - __5A - __6A - __7A VAR __8Table = ADDCOLUMNS(__Convert,"Diff",__8 - [Arabic]) VAR __8Min = MINX(FILTER(__8Table,[Diff] >= 0),[Diff]) VAR __8R = MAXX(FILTER(__8Table,[Diff] = __8Min),[Roman]) VAR __8A = MAXX(FILTER(__8Table,[Diff] = __8Min),[Arabic]) // 9th digit VAR __9 = __Value - __1A - __2A - __3A - __4A - __5A - __6A - __7A - __8A VAR __9Table = ADDCOLUMNS(__Convert,"Diff",__9 - [Arabic]) VAR __9Min = MINX(FILTER(__9Table,[Diff] >= 0),[Diff]) VAR __9R = MAXX(FILTER(__9Table,[Diff] = __9Min),[Roman]) VAR __9A = MAXX(FILTER(__9Table,[Diff] = __9Min),[Arabic]) // 10th digit VAR __10 = __Value - __1A - __2A - __3A - __4A - __5A - __6A - __7A - __8A - __9A VAR __10Table = ADDCOLUMNS(__Convert,"Diff",__10 - [Arabic]) VAR __10Min = MINX(FILTER(__10Table,[Diff] >= 0),[Diff]) VAR __10R = MAXX(FILTER(__10Table,[Diff] = __10Min),[Roman]) VAR __10A = MAXX(FILTER(__10Table,[Diff] = __10Min),[Arabic]) // 11th digit VAR __11 = __Value - __1A - __2A - __3A - __4A - __5A - __6A - __7A - __8A - __9A - __10A VAR __11Table = ADDCOLUMNS(__Convert,"Diff",__11 - [Arabic]) VAR __11Min = MINX(FILTER(__11Table,[Diff] >= 0),[Diff]) VAR __11R = MAXX(FILTER(__11Table,[Diff] = __11Min),[Roman]) VAR __11A = MAXX(FILTER(__11Table,[Diff] = __11Min),[Arabic]) // 12th digit VAR __12 = __Value - __1A - __2A - __3A - __4A - __5A - __6A - __7A - __8A - __9A - __10A - __11A VAR __12Table = ADDCOLUMNS(__Convert,"Diff",__12 - [Arabic]) VAR __12Min = MINX(FILTER(__12Table,[Diff] >= 0),[Diff]) VAR __12R = MAXX(FILTER(__12Table,[Diff] = __12Min),[Roman]) VAR __12A = MAXX(FILTER(__12Table,[Diff] = __12Min),[Arabic]) // 13th digit VAR __13 = __Value - __1A - __2A - __3A - __4A - __5A - __6A - __7A - __8A - __9A - __10A - __11A - __12A VAR __13Table = ADDCOLUMNS(__Convert,"Diff",__13 - [Arabic]) VAR __13Min = MINX(FILTER(__13Table,[Diff] >= 0),[Diff]) VAR __13R = MAXX(FILTER(__13Table,[Diff] = __13Min),[Roman]) VAR __13A = MAXX(FILTER(__13Table,[Diff] = __13Min),[Arabic]) // 14th digit VAR __14 = __Value - __1A - __2A - __3A - __4A - __5A - __6A - __7A - __8A - __9A - __10A - __11A - __12A - __13A VAR __14Table = ADDCOLUMNS(__Convert,"Diff",__14 - [Arabic]) VAR __14Min = MINX(FILTER(__14Table,[Diff] >= 0),[Diff]) VAR __14R = MAXX(FILTER(__14Table,[Diff] = __14Min),[Roman]) VAR __14A = MAXX(FILTER(__14Table,[Diff] = __14Min),[Arabic]) // 15th digit VAR __15 = __Value - __1A - __2A - __3A - __4A - __5A - __6A - __7A - __8A - __9A - __10A - __11A - __12A - __13A - __14A VAR __15Table = ADDCOLUMNS(__Convert,"Diff",__15 - [Arabic]) VAR __15Min = MINX(FILTER(__15Table,[Diff] >= 0),[Diff]) VAR __15R = MAXX(FILTER(__15Table,[Diff] = __15Min),[Roman]) VAR __15A = MAXX(FILTER(__15Table,[Diff] = __15Min),[Arabic]) // 16th digit VAR __16 = __Value - __1A - __2A - __3A - __4A - __5A - __6A - __7A - __8A - __9A - __10A - __11A - __12A - __13A - __14A - __15A VAR __16Table = ADDCOLUMNS(__Convert,"Diff",__16 - [Arabic]) VAR __16Min = MINX(FILTER(__16Table,[Diff] >= 0),[Diff]) VAR __16R = MAXX(FILTER(__16Table,[Diff] = __16Min),[Roman]) VAR __16A = MAXX(FILTER(__16Table,[Diff] = __16Min),[Arabic]) // 17th digit VAR __17 = __Value - __1A - __2A - __3A - __4A - __5A - __6A - __7A - __8A - __9A - __10A - __11A - __12A - __13A - __14A - __15A - __16A VAR __17Table = ADDCOLUMNS(__Convert,"Diff",__17 - [Arabic]) VAR __17Min = MINX(FILTER(__17Table,[Diff] >= 0),[Diff]) VAR __17R = MAXX(FILTER(__17Table,[Diff] = __17Min),[Roman]) VAR __17A = MAXX(FILTER(__17Table,[Diff] = __17Min),[Arabic]) // 18th digit VAR __18 = __Value - __1A - __2A - __3A - __4A - __5A - __6A - __7A - __8A - __9A - __10A - __11A - __12A - __13A - __14A - __15A - __16A - __17A VAR __18Table = ADDCOLUMNS(__Convert,"Diff",__18 - [Arabic]) VAR __18Min = MINX(FILTER(__18Table,[Diff] >= 0),[Diff]) VAR __18R = MAXX(FILTER(__18Table,[Diff] = __18Min),[Roman]) VAR __18A = MAXX(FILTER(__18Table,[Diff] = __18Min),[Arabic]) // 19th digit VAR __19 = __Value - __1A - __2A - __3A - __4A - __5A - __6A - __7A - __8A - __9A - __10A - __11A - __12A - __13A - __14A - __15A - __16A - __17A - __18A VAR __19Table = ADDCOLUMNS(__Convert,"Diff",__19 - [Arabic]) VAR __19Min = MINX(FILTER(__19Table,[Diff] >= 0),[Diff]) VAR __19R = MAXX(FILTER(__19Table,[Diff] = __19Min),[Roman]) VAR __19A = MAXX(FILTER(__19Table,[Diff] = __19Min),[Arabic]) // 20th digit VAR __20 = __Value - __1A - __2A - __3A - __4A - __5A - __6A - __7A - __8A - __9A - __10A - __11A - __12A - __13A - __14A - __15A - __16A - __17A - __18A - __19A VAR __20Table = ADDCOLUMNS(__Convert,"Diff",__20 - [Arabic]) VAR __20Min = MINX(FILTER(__20Table,[Diff] >= 0),[Diff]) VAR __20R = MAXX(FILTER(__20Table,[Diff] = __20Min),[Roman]) VAR __20A = MAXX(FILTER(__20Table,[Diff] = __20Min),[Arabic]) RETURN CONCATENATEX( { __1R, __2R, __3R, __4R, __5R, __6R, __7R, __8R, __9R, __10R, __11R, __12R, __13R, __14R, __15R, __16R, __17R, __18R, __19R, __20R }, [Value] )

eyJrIjoiNTA0ZTE5MzEtNzBjMi00NzdhLTk4MTMtNzQ3YWIwNWNlY2ZkIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

Re: ROMAN

Anonymous — Mon, 07 Feb 2022 18:20:22 GMT

Hi Greg,

many thanks for your quick measures.

Roman number is also a fine peice of code. But this approach can't be used in a real report like sales. Maybe it would be possible to extent it by the roman way of thousands?

Elementary Math Tricks (synonym.com)

How to write one million in Roman numerals - Quora

But I have no clue if it is possible to overline a character in DAX, some examples from wikipedia

IV = 4,000
IVDCXXVII = 4,627
XXV = 25,000
XXVCDLIX = 25,459

Many thanks for your work

Kind regards

Matthias

Re: ROMAN

Greg_Deckler — Mon, 07 Feb 2022 22:14:07 GMT

Anonymous Not a lot of bar symbols available but there is an X bar UNICODE character: X Bar Symbol (x̄) (wumbo.net). There is also a reversed C character: Unicode Character 'LATIN SMALL LETTER REVERSED C' (U+2184) (fileformat.info).

So, in theory could add the appropriate notation to the conversion table for larger numbers.

Re: ROMAN

Anonymous — Tue, 08 Feb 2022 08:57:37 GMT

There is a possibility to combine unicode characters

Roman_million = "M" & UNICHAR(772)

Roman_million = "M" & UNICHAR(773)

topic Re: ROMAN in Quick Measures Gallery

ROMAN

Re: ROMAN

Re: ROMAN

Re: ROMAN