Forum Discussion
GROUP BY calculated column
May be
Calculated_Column = VAR InvoiceNo = [Invoice No] RETURN CONCATENATEX ( FILTER ( Sales, Sales[Invoice No] = InvoiceNo ), Sales[Salesperson], ", " )Zubair_Muhammad thanks for looking into this, this works, but coming up with duplicates. Is there a way to suppress duplicates?
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.
Anonymous thanks, I tried that route but could not quite get it as there is no delimiter after unpivot. Is there a DAX way?
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.
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.
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], ", " )