DAX Function to Concatenate Distinct Values from a Dimension Grouped by a Different Dimension Column

[HRegnum]

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

[v-junyant-msft]

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

[123abc]

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
CategoryList

In this formula:

1. 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.

2. 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.

3. We then use the FILTER function to filter the summarized table for the specific employee based on the EmployeeID variable.

4. 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.

[HRegnum]

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:

1. We start by defining a variable, CurrentProductKey, to capture the ProductKey of the current row in the DimProduct table.

2. 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.

3. 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.

[HRegnum]

Thank you for your follow up and I am still gettin blank values for categories where the employee did not sell and also repetitive values

I applied your suggestion (below is the DAX formula) and the outcome is in the screenshot:

Cats =

VAR CurrentProductKey = 'DimProduct'[productkey]

RETURN

CONCATENATEX(

            FILTER(

                SUMMARIZE('Sales', 'DimEmployee'[empID], 'DimEmployee'[FN], 'DimEmployee'[LN], 'DimProduct'[productcategory]),

                'DimProduct'[productkey] = CurrentProductKey

            ),

            'DimProduct'[productcategory],

            ", "

)

[123abc]

I see that you're still experiencing issues with repetitive values and blank values for categories where the employee did not sell. To address these problems, you can modify the DAX formula to ensure distinct values are concatenated and to handle the case of employees not selling certain categories.

You can use the following DAX formula for your calculated column in the DimProduct table:

Cats =
VAR CurrentProductKey = 'DimProduct'[productkey]
RETURN
CONCATENATEX(
FILTER(
SUMMARIZE('Sales', 'DimEmployee'[empID], 'DimEmployee'[FN], 'DimEmployee'[LN], 'DimProduct'[productcategory]),
'DimProduct'[productkey] = CurrentProductKey
),
DISTINCT('Sales'[productcategory]),
", "
)

In this modified formula:

1. We use the DISTINCT function to ensure that only distinct product categories are concatenated.

2. This formula will handle cases where an employee did not sell a category by only concatenating the distinct categories sold by that employee.

By using the DISTINCT function, you should eliminate repetitive values, and it should also handle the issue of blank values for categories not sold by the employee. This should provide the desired outcome in your DimProduct table.

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.

[HRegnum]

I have tried this before and it's gives an error that you can't supply multiple values where a single value is expected.

I have asked ChatGPT for help and it had suggested the same solutions you did.

I appreciate if there's another approach about this.

Thanks.

[123abc]

I apologize for any confusion. It seems that the suggested approach isn't working as expected due to the limitations of calculated columns in Power BI when dealing with many-to-many relationships and multiple values.

If you have a many-to-many relationship between DimProduct and DimEmployee and are attempting to create a calculated column in the DimProduct table, it can be challenging to achieve the desired outcome directly with calculated columns due to their limitations.

One alternative approach is to use a measure or a calculated table in a visual or in a separate table to display the concatenated distinct values. You can create a measure in Power BI that aggregates the values for display in a table or a visual. This approach should allow you to bypass the limitations of calculated columns and many-to-many relationships. Here's a simplified example:

1. Create a new table or measure in Power BI using DAX. For instance, create a measure:

Concatenated Categories =
CONCATENATEX(
VALUES('DimProduct'[productkey]),
CONCATENATEX(
FILTER('DimSales', RELATED('DimProduct'[productkey]) = 'DimProduct'[productkey]),
'DimProduct'[productcategory],
", "
),
", "
)

1. Then, in your visual or table, place the Employee First Name and Employee Last Name from the DimEmployee table and the Concatenated Categories measure from step 1. This should give you the desired outcome.

Keep in mind that this approach uses a measure, so it operates in the context of your visualizations, providing a more flexible solution compared to a calculated column.

Please note that performance considerations should be taken into account if you have a large dataset, as measures are calculated on the fly during visualization rendering and can impact report performance.

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.