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

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      6 years ago

      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
  )