Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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?
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
As part of the import query you could extract this and then unpivot that data. I'd look into these functions under Edit Query:
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], ", " )
@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.
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |