Calculated column analysis

[isThisABug]

Hello everyone, I wante to post this in order to improve my skills and get a better understanding of Power BI.

The problem I solved is create a calculated column that concatenates other columns that have written one product name, so we have column 1 = product 1, column 2 = product 2... until 5 in my case. The tricky part is that I want to display the result in alphabetic order to be able to do some calculations based on the columns without having duplicated data because the name of the products are not ordered at the beginning.

So this is the code I created:

cadena productos ordenados = 

var p1 = 'Table'[Product 1]
return

var p2 = 'Table'[Product 2]
return

var p3 = 'Table'[Product 3]
return

var p4 = 'Table'[Product 4]
return

var p5 = 'Table'[Product 5]
return

var word5 = 
MAX(
    MAX(
        MAX(
            MAX(
                p1;
                p2
            );
            p3
        );
        p4
    );
    p5
)
return

var word4 =
MAX(
    MAX(
        MAX(
            MAX(
                IF(p1=word5;BLANK();p1);
                IF(p2=word5;BLANK();p2)
            );
            IF(p3=word5;BLANK();p3)
        );
        IF(p4=word5;BLANK();p4)
    );
    IF(p5=word5;BLANK();p5)
)
return

var word3 =
MAX(
    MAX(
        MAX(
            MAX(
                IF(OR(p1=word5;p1=word4);BLANK();p1);
                IF(OR(p2=word5;p2=word4);BLANK();p2)
            );
            IF(OR(p3=word5;p3=word4);BLANK();p3)
        );
        IF(OR(p4=word5;p4=word4);BLANK();p4)
    );
    IF(OR(p5=word5;p5=word4);BLANK();p5)
)
return

var word2 =
MAX(
    MAX(
        MAX(
            MAX(
                IF(OR(p1=word3;OR(p1=word5;p1=word4));BLANK();p1);
                IF(OR(p2=word3;OR(p2=word5;p2=word4));BLANK();p2)
            );
            IF(OR(p3=word3;OR(p3=word5;p3=word4));BLANK();p3)
        );
        IF(OR(p4=word3;OR(p4=word5;p4=word4));BLANK();p4)
    );
    IF(OR(p5=word3;OR(p5=word5;p5=word4));BLANK();p5)
)
return

var word1 =
MAX(
    MAX(
        MAX(
            MAX(
                IF(OR(p1=word2;OR(p1=word3;OR(p1=word5;p1=word4)));BLANK();p1);
                IF(OR(p2=word2;OR(p2=word3;OR(p2=word5;p2=word4)));BLANK();p2)
            );
            IF(OR(p3=word2;OR(p3=word3;OR(p3=word5;p3=word4)));BLANK();p3)
        );
        IF(OR(p4=word2;OR(p4=word3;OR(p4=word5;p4=word4)));BLANK();p4)
    );
    IF(OR(p5=word2;OR(p5=word3;OR(p5=word5;p5=word4)));BLANK();p5)
)
return

CONCATENATE(
    word1;
    CONCATENATE(
        IF(ISBLANK(word1);BLANK();", ");
        CONCATENATE(
            word2;
            CONCATENATE(
                IF(ISBLANK(word2);BLANK();", ");
                CONCATENATE(
                    word3;
                    CONCATENATE(
                        IF(ISBLANK(word3);BLANK();", ");
                        CONCATENATE(
                            word4;
                            CONCATENATE(
                                IF(ISBLANK(word4);BLANK();", ");
                                word5
                            )
                        )
                    )
                )
            )
        )
    )
)

I think this code can be improved with maybe virtual tables but I don't know yet how to use them properly. I will be happy to receive any feedback from you.

Thank you!

[Anonymous]

DAX? Really? Use Power Query. Don't try to squeeze a square peg into a round hole.

Best
D

[Greg_Deckler]

Oh yeah, duh, forgot CONCATENATEX has an order by expression:

Sort 3 = 
    VAR __Table =  { [Product 1], [Product 2], [Product 3], [Product 4], [Product 5] }
RETURN
    CONCATENATEX(__Table,[Value],",",[Value],ASC)

Or, in a single line:

Sort 4 = CONCATENATEX({ [Product 1], [Product 2], [Product 3], [Product 4], [Product 5] },[Value],",",[Value],ASC)
    

@Anonymous I'm not sure why a single line of DAX code is a square peg. I mean, you could possibly do it in a single line of M code, but I don't see that being any better than a single line of DAX code.

[Anonymous]

Dear Mr. @Greg_Deckler,

First, because this is data mashup. Second, because DAX calc columns are not optimally compressed. Third, because in M you don't have to hard-code the number of products. The other reasons I'm sure you'll be able to figure out all by yourself.

Is this not enough to understand that DAX is a Data Analysis eXpressions language not fit for data mashup operations? By the way, data workflows use M, not DAX. FOR A GOOD REASON.

That something is possible in a language does not mean it should be done.

Best
D

[Greg_Deckler]

Except that if you read the very next post @Anonymous , I didn't have to hard code anything. There is literally a single function in as in 1 function, uno Función, one function that does exactly what @isThisABug is trying to do. So how does a having a single function that is explicitly designed to do exactly the job that is trying to be done constitute a square peg? This is like calling a hammer a "square peg" for the job of pounding a nail. The CONCATENATEX function was literally designed to do the exact job at hand. It wasn't an accident that there are parameters for sorting. It isn't just a novel use of something that wasn't designed for purpose. There is literally no other reason that sorting functionality would have been included in CONCATENATEX other than to solve this problem. So, calling it a square peg is nonsense. It's absolute nonsense.

Plus, the original ask was "how to use DAX table expressions to improve the code". I may be mis-reading things but I don't believe it said "Hey, please chastise me".

Are there advantages and disadvantages to both approaches. Yes. But calling DAX a square peg when it has a function that has been specifically engineered, and I mean purpose built, to accomplish the task at hand is just being...well...I'll just leave it there.

And what do dataflows have to do with anything and data mashup. Yeeesss, of course Microsoft would use M code for data flows because it's literally the same operation as importing data... So... Surely a simple calculated column using a function purpose built for the task is not data mashup? At least not in the same vein as true data mashup.

And BTW this is an unwinnable argument. There is nothing that is possible today in Python or R or Ruby or any other trendy programming language that couldn't be done in any earlier programming language like C or Java or for that matter COBOL or Fortran. It all reduces to 1's and 0's eventually but shockingly people still use the language they feel like using in the end. I could just as easily make an argument that everyone that uses Power Query instead of EMML is crazy but it would all just be pontification and hot air because nobody really cares as long as they can get the job done. I mean Power Query...really? It doesn't even have mashup design portability and interoperability of mashup solutions. What kind of noob would use such a thing? What a terrible tool for the job! I mean Power Query is laughably inferior! There's no embedded scripting support for JavaScript, JRuby, Groovy or XQuery. And no parallel syntax for concurrent processing. I mean, you would have to be a complete and utter maroon to use Power Query for something like data mashup, it's completely ill suited for the task compared to EMML!

And hence why these kinds of debates are pointless...

[Anonymous]

Yeah... Discussion with you truly is pointless.

There are different programming languages for different purposes. Doing everything in one is a sign you don't understand a single thing about programming. The fact that something is possible in a language does not mean it should be done. I don't have time to explain such simple things.

Your tirade above only shows too clearly you've got a lot to learn, Mr. Gregory.

Best
D

[Greg_Deckler]

Just so we're clear then @Anonymous , so that we can all agree and be friends, having a native, built-in function in a programming language that is purpose built for a particular task and then using said pupose built function for exactly the task for which it was designed constitutes putting a square peg in a round hole. Right? That's what we're going with? Everyone agrees with that?

[Greg_Deckler]

Here is the M code:

Text.Combine( List.Sort({[Product 1], [Product 2], [Product 3], [Product 4], [Product 5]} ),"," )

M requires 2 functions to do what DAX can do in 1 function. Just, just pointing that out... 🙂 I suppose one of them is a square peg...

[isThisABug]

First of all thank you so much for your fast reply!

Your code was so helpful, the DAX and the M version. I am quite new on this world and I had no idea of those 2 languages 4 months ago, I am not used to this kind of programming.

At the end I got the M version because of the correct handling of commas. The DAX was my first option but in some columns the cell is empty and I need to display it as blank and with the DAX code I will need to implement again the BLANK() hanlding, I didn't mentioned it earlier I am sorry. With the M code it was done automatically so that's why I have chosen it.

Thank you so much for real,

Best regards!

[Greg_Deckler]

@isThisABug - Makes total sense to me, In DAX you could have done that like this, just an FYI:

Sort 4 = CONCATENATEX( FILTER({ [Product 1], [Product 2], [Product 3], [Product 4], [Product 5] },NOT(ISBLANK([Value]))),[Value],",",[Value],ASC)

At this point you would probably want to write in a nicely formatted form like:

Sort 4 = 
  CONCATENATEX( 
    FILTER(
      { [Product 1], [Product 2], [Product 3], [Product 4], [Product 5] },
      NOT(ISBLANK([Value]))
    ),
   [Value],",",[Value],ASC
  )

[Greg_Deckler]

Well, @isThisABug here is a potential improvement. I'm not entirely happy with it because it is still tied to a reasonable maximum approach, you have to code it for the number of items in your sort. But, it is potentially a starting place. PBIX is attached.

Column = 
    VAR __Table = { [Product 1], [Product 2], [Product 3], [Product 4], [Product 5] }
    VAR __word5 = MAXX(__Table,[Value])
    VAR __word4 = MAXX(EXCEPT(__Table,{ __word5 }),[Value])
    VAR __word3 = MAXX(EXCEPT(__Table,{ __word5, __word4 }),[Value])
    VAR __word2 = MAXX(EXCEPT(__Table,{ __word5, __word4, __word3 }),[Value])
    VAR __word1 = MINX(__Table,[Value])
RETURN
    CONCATENATEX({__word1, __word2, __word3, __word4, __word5 },[Value],",")

I like your implementation of a bubble sort, I'm basically using the same technique.