cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
isThisABug
Frequent Visitor

Calculated column analysis

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!

10 REPLIES 10
Anonymous
Not applicable

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

Best
D

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.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition
Anonymous
Not applicable

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

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...


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition
Anonymous
Not applicable

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

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?


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

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...


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

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!

@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
  )

 


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors