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

ek2112's avatar
ek2112
Advocate II
8 years ago

Anonymous I have the data in seperate columns as indicated in my example above, in a spreadsheet. There is no delimiter like in a csv.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Ok, so you are trying to create the last column, its not part of your initial dataset.

 

Here is some dax code that should be close to what you are looking for. You might need to add more variables if you have more constraints on what to be placed on which lines a sales persons name gets placed on.

 

Calculated Column = var InvoiceNo = [Invoice No]
RETURN
CONCATENATEX(
	CALCULATE(
		DISTINCT('YourTable'[SalesPerson]),
		ALL('YourTable'),
		'YourTable'[Invoice No] = InvoiceNo
	),
	[SalesPerson],
	", "	
)
  • ek2112's avatar
    ek2112
    Advocate II
    8 years ago

    Anonymous Thanks, I tried with the measure, but I get an error "table of multiple values was supplied where a single value was expected". Here is the sample pbix.

     

     

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      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
        Advocate II
        8 years ago

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