Create new table after selecting slicer

[Neko_Daisuki]

I have the following two tables.
#Product

Product Category	Product Code	Product Name
a brush	p700	brush k
b chancon	p901	shampoo y
a brush	p902	combo x
c mask	p712	hair mask v

#Purchase history

Purchase date	Member ID	Product Code	Product CD	Number of purchases	Index
20210210	c10	p700	5000	1	1
20230210	c10	p901	1000	1	2
20230216	c10	p902	2400	3	3
20230616	c10	p901	1000	2	4
20231016	c10	p901	1000	3	5
20220410	c28	p901	1000	1	6
20221216	c28	p902	2400	3	7
20230816	c28	p901	1000	2	8
20231121	c28	p901	1000	3	9
20190410	c01	p712	1000	1	10
20221216	c01	p902	2400	3	11
20230816	c01	p901	1000	1	12
20230921	c01	p901	1000	2	13

I have two slicers installed.
From Product, "b chancon" in Product Category
From Purchase history, Number of purchases is "1"

Once the slicer selection is complete, I would like to use SUMMARIZECOLUMNS and ADDCOLUMNS to output the following results to a separate table.

Purchase date	Member ID	Product Code	Purchase amount	Number of purchases	Index
20210210	c10	p700	5000	1	1
20190410	c01	p712	1000	1	10
20221216	c01	p902	2400	3	11

The output logic is that when you select "b chancon", the Product Code
is "p901".
Based on the results, we are looking for a way to identify customers who have purchased the product and display only their past purchase history.
Thanks for advance.

[Neko_Daisuki]

Dear @123abc 

It wasn't the result I expected, but after making a few adjustments, I got the result I described.
Thank you for all your support.
I look forward to working with you.

[123abc]

To achieve the desired output, you can use the following DAX formula in Power BI. Assuming that you want to create a new table named "FilteredPurchaseHistory" based on the slicer selections:

FilteredPurchaseHistory =
VAR SelectedProductCategory = VALUES('Product'[Product Category])
VAR SelectedNumberOfPurchases = VALUES('Purchase history'[Number of purchases])
RETURN
ADDCOLUMNS(
SUMMARIZECOLUMNS(
'Purchase history'[Purchase date],
'Purchase history'[Member ID],
'Purchase history'[Product Code],
'Purchase history'[Number of purchases],
'Purchase history'[Index]
),
"ProductCategory", CALCULATE(MAX('Product'[Product Category]), 'Product'[Product Code] = 'Purchase history'[Product Code]),
"FilterCondition",
'Product'[Product Category] = SelectedProductCategory &&
'Purchase history'[Number of purchases] = SelectedNumberOfPurchases
)

This formula creates a new table by summarizing the relevant columns from the 'Purchase history' table and adds a couple of calculated columns:

1. "ProductCategory": It retrieves the corresponding 'Product Category' for each 'Product Code' in the 'Purchase history' table.
2. "FilterCondition": It checks if the selected product category and the number of purchases match the slicer selections.

The result is a table that includes only the rows where the product category matches the slicer selection, and the number of purchases is as selected. You can then use this new table for your analysis.

Note: Make sure to replace 'Product' and 'Purchase history' with the actual names of your tables in your Power BI model.

[Neko_Daisuki]

Dear 123abc,

Thank you for reaching out to us. We attempted to create a table using the DAX you provided.
However, we encountered an error due to multiple and duplicate items in "'Product'[Product Category]."

Product Category	Product Code	Product Name
a brush	p700	brush k
b chancon	p901	shampoo y
a brush	p902	combo x
c mask	p712	hair mask v

The error message is as follows:

"Cannot determine a single value for column 'Product Category' in table 'Product'. This may occur when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

Could you possibly provide a solution for this issue?

[123abc · Accepted solution]

I apologize for the oversight. It seems that the 'Product Category' column has multiple values for the selected product, which is causing the error. To resolve this, we can modify the DAX code to handle this situation.

We can use the CALCULATETABLE function along with the VALUES function to filter the 'Product' table based on the selected 'Product Category' in the slicer. Here's an updated DAX code:

NewTable =
VAR SelectedProductCategory =
VALUES ( 'Product'[Product Category] )
VAR SelectedNumberOfPurchases =
VALUES ( 'Purchase history'[Number of purchases] )
RETURN
CALCULATETABLE (
SUMMARIZECOLUMNS (
'Purchase history'[Purchase date],
'Purchase history'[Member ID],
'Purchase history'[Product Code],
'Purchase history'[Number of purchases],
'Purchase history'[Index],
'Product'[Product Code],
'Product'[Product Category],
'Product'[Product Name],
'Purchase history'[Product CD],
'Purchase history'[Number of purchases],
'Purchase history'[Index],
'Purchase history'[Purchase amount]
),
'Product'[Product Category] IN SelectedProductCategory
)

This modification uses CALCULATETABLE to filter the 'Product' table based on the selected 'Product Category' from the slicer. The IN clause ensures that only rows with 'Product Category' values matching the selected category are included in the final result.

Please replace the column names with the actual column names from your dataset. Adjust the code accordingly based on your Power BI model and relationships. This modification should address the issue of multiple values in the 'Product Category' column.

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.