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.
Microsoft EmployeeDear 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?
Community ChampionI 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.