Forum Discussion

ek2112's avatar
ek2112
Icon for Advocate II rankAdvocate 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

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
Icon for Advocate II rankAdvocate 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
    Icon for Community Champion rankCommunity 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
      Icon for Advocate II rankAdvocate 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?

      • Vvelarde's avatar
        Vvelarde
        Icon for Community Champion rankCommunity Champion
        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