Forum Discussion

isThisABug's avatar
isThisABug
Frequent Visitor
6 years ago

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 t...
Show and Tell
Tips and Tricks
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
6 years ago
DAX? Really? Use Power Query. Don't try to squeeze a square peg into a round hole.

Best
D
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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.

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      6 years ago

      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's avatar
        isThisABug
        Frequent Visitor
        6 years ago

        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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Greg_Deckler
        Community Champion
        6 years ago

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