Forum Discussion
Microsoft EmployeeCreate new table after selecting slicer
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.
Community ChampionTo 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:
- "ProductCategory": It retrieves the corresponding 'Product Category' for each 'Product Code' in the 'Purchase history' table.
- "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_DaisukiDear 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
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.