Forum Discussion
DAX Function to Concatenate Distinct Values from a Dimension Grouped by a Different Dimension Column
Hello,
Looking for the Community's help on this.
I have the below data model. I want to create a column with DAX to concatenate product categories form DimProduct by the Employee to show all the categories that this employee sold.
What I got from CONCATENATEX is repeated values and blank values of categories an Employee did not sell.
Appreciate your help on this.
Desired Outcome:
| Employee First Name | Employee Last Name | Categories Sold |
| John | Doe | A, B, C, D |
| Jane | Doe | B, C |
| Lord | Voldemort | D |
| Severus | Snape | A, C |
EDIT (26-10-2023): I am trying to create this a DAX column in the DimProduct table.
Data Model
8 Replies
- 123abc
Community Champion
To achieve the desired outcome of concatenating distinct product categories for each employee, you can use the DAX function SUMMARIZECOLUMNS in combination with CONCATENATEX. Here's a DAX formula to create a calculated column for your scenario:
Categories Sold =
VAR EmployeeID = 'Sales'[Employee ID]
VAR CategoryList =
CONCATENATEX(
FILTER(
SUMMARIZECOLUMNS('Sales'[Product Category]),
'Sales'[Employee ID] = EmployeeID
),
'Sales'[Product Category],
", "
)
RETURN
CategoryListIn this formula:
We first define a variable EmployeeID to store the current employee's ID. You'll need to replace 'Sales'[Employee ID] with the actual column reference to your employee's ID.
Next, we use the SUMMARIZECOLUMNS function to create a summary table that contains distinct product categories for the given employee. This ensures that you won't get repeated or blank values.
We then use the FILTER function to filter the summarized table for the specific employee based on the EmployeeID variable.
Finally, we use CONCATENATEX to concatenate the distinct product categories from the filtered table, separating them with a comma and a space.
Make sure to replace 'Sales'[Employee ID] and 'Sales'[Product Category] with the actual column references in your data model. Once you create this calculated column, it should give you the desired outcome with concatenated distinct product categories for each employee.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
I am trying to create this as a column in the DimProduct Table.
I am not able to add any field outside of the context of the DimProduct table in this case.
is there a solution of this?- 123abc
I see that you want to create a calculated column in the DimProduct table to display the concatenated categories sold for each employee within that table's context. To achieve this, you can use the RELATEDTABLE and EARLIER functions to iterate through the FactSales table to find distinct product categories sold by each employee associated with the product. Here's the DAX formula to use as a calculated column in the DimProduct table:
Categories Sold =
VAR CurrentProductKey = DimProduct[ProductKey]
RETURN
CONCATENATEX(
FILTER(
SUMMARIZE(
FactSales,
DimEmployee[EmployeeKey],
DimEmployee[Employee First Name],
DimEmployee[Employee Last Name],
DimProduct[Category]
),
DimProduct[ProductKey] = CurrentProductKey
),
DimProduct[Category],
", "
)Here's how this formula works:
We start by defining a variable, CurrentProductKey, to capture the ProductKey of the current row in the DimProduct table.
Then, we use FILTER to create a table that contains a summary of the FactSales data, including the EmployeeKey, Employee First Name, Employee Last Name, and Category. We filter this summary table based on the ProductKey matching the current product's ProductKey.
Finally, we use CONCATENATEX to concatenate the distinct categories sold within the filtered summary table and store the result in the Categories Sold column for each product in the DimProduct table.
This formula should give you the desired outcome by creating a calculated column in the DimProduct table, showing the concatenated list of distinct product categories sold for each product.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
Hi HRegnum ，
I'm sorry that you only provided the table structure, and I can't help you with testing in Desktop. Can you please provide some DimEmployee, DimProduct, and some sample data related to the results you need (in order to protect your data privacy, if you are willing to provide data, please delete the part of the data related to privacy, or replace it with sample data).
Best Regards,
Dino Tao
