Forum Discussion

ek2112's avatar
ek2112
Advocate II
8 years ago
Solved

GROUP BY calculated column

Hello,

 

I have the data in the following format - is there a way to extract all salesperson names and write them into a new calculated column for each invoice number? Thank you.

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    ek2112

     

    May be

     

    Calculated_Column =
VAR InvoiceNo = [Invoice No]
RETURN
    CONCATENATEX (
        FILTER ( Sales, Sales[Invoice No] = InvoiceNo ),
        Sales[Salesperson],
        ", "
    )
  • ek2112's avatar
    ek2112
    8 years ago

    Zubair_Muhammad thanks for looking into this, this works, but coming up with duplicates. Is there a way to suppress duplicates?

  • Vvelarde's avatar
    Vvelarde
    8 years ago

    ek2112

     

    Hi, try with this:

     

    Column =
VAR Invoice = Table1[Invoice N]
RETURN
    CONCATENATEX (
        CALCULATETABLE (
            DISTINCT ( Table1[Sales Person] ),
            FILTER ( Table1, Table1[Invoice N] = Invoice )
        ),
        Table1[Sales Person],
        ","
    )

    Regards

     

    Victor

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    As part of the import query you could extract this and then unpivot that data.  I'd look into these functions under Edit Query:

     

    • Split column by Delimiter
    • Unpivot Columns

     

    Both are found under the Transform area of the Ribbon menu.

     

    • ek2112's avatar
      ek2112
      Advocate II
      8 years ago

      Anonymous thanks, I tried that route but could not quite get it as there is no delimiter after unpivot. Is there a DAX way?

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        I might be misunderstanding your dataset, but my expectation is that you have values in a column such as "A, B, C" which you are trying to split out.  My expectation is that you want to split that using the delmiter of a comma.  That is going to give you multiple columns, from there you would unpivot that data.