GROUP BY calculated column

[ek2112]

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.

[Zubair_Muhammad · Accepted solution]

@ek2112

May be

Calculated_Column =
VAR InvoiceNo = [Invoice No]
RETURN
    CONCATENATEX (
        FILTER ( Sales, Sales[Invoice No] = InvoiceNo ),
        Sales[Salesperson],
        ", "
    )

[ek2112 · Accepted solution]

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

[Anonymous]

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]

@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]

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.

[ek2112]

@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]

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]

@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 · Accepted solution]

@ek2112

May be

Calculated_Column =
VAR InvoiceNo = [Invoice No]
RETURN
    CONCATENATEX (
        FILTER ( Sales, Sales[Invoice No] = InvoiceNo ),
        Sales[Salesperson],
        ", "
    )

[ek2112 · Accepted solution]

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

[Vvelarde · Accepted solution]

@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]

@Vvelarde perfect, worked like a charm. Thank you.

@Zubair_Muhammad @Anonymous thank you.