Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Greg_Deckler
Super User
Super User

I think I broke DAX

So I have been making decent progress on my little project to come up with a translation of as many Excel functions to DAX as possible. Then I ran into ROMAN. Not pretty, but a solvable problem using a little brute force. I generally develop these things as columns first and then convert them to measures. So, in column form, this thing returns in like under 5 seconds. But, translating it to a measure, even if I filter the table down to only a single value to convert, I can't get it to return, it just spins. And, when I try to save the file, well, I've been waiting for 30+ minutes and it is still saving. @marcorusso you are the smartest person about DAX that I know, can you shed any light on what is going on here? 

 

@Mariusz @MFelix  @amitchandak  @edhans @ImkeF @Anonymous @parry2k  @VasTg @az38 @HotChilli @Ashish_Mathur @AllisonKennedy @camargos88 anyone have an idea what is going on?

 

Here are the steps to reproduce:

  1. Create a table using the following formula: Arabic = GENERATESERIES(1,4000)
  2. Paste in the code below as a calculated column, values are displayed in under 5 seconds
  3. Create a measure changing the top line to be VAR __Value = MAX('Arabic'[Value])
  4. Put 'Arabic'[Value] in a Table visualization, set to Don't summarize and filter to 1
  5. Put Measure in Table visual, watch it spin for forever
  6. Try to save the file, forever

 

 

Roman Numeral = 
    VAR __Value = '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
//__Value & "," & __1 & "," & __2 & "," & __3 & "," & __4 & "," & __5 & "," & __6 & "," & __7Min & ", " & __8 & ", " & __9 & "," & __10
    CONCATENATEX(
        { __1R, __2R, __3R, __4R, __5R, __6R, __7R, __8R, __9R, __10R, __11R, __12R, __13R, __14R, __15R, __16R, __17R, __18R, __19R, __20R },
        [Value]
    )

 

 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
3 REPLIES 3

Very interesting!

I analyzed the problem and my finding is that SUMMARIZECOLUMNS has a problem with this measure.

The measure works well with other table functions.

You should open a support ticket with Microsoft - I suggest you creating a small Power BI file that reproduce the problem and open an official ticket, so it could be prioritized and identified in the Microsoft support chain, until it reaches the development team. Even though we find a workaround, this should be fixed because I'm worried it could affect other expressions.

Please write me by email privately.

 

Marco

Ugh.... SUMMARIZECOLUMNS().....



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

I have posted the Issue here: https://community.powerbi.com/t5/Issues/Issue-with-DAX-Measure-Perhaps-a-SUMMARIZECOLUMNS-issue/idi-...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.