Dear Power BI Representative,
I am reviewing Chapter 13 of Authoring Queries within 'The Definitive Guide To DAX' guidebook and
I received this error message when I tried to enter the expression.:
Query (848, 4) A single value for column 'Category' in table 'Product' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as a min, max, count, or sum to get a single result.
Query batch completed with errors
EVALUATE
TOPN (
501,
SELECTCOLUMNS ( /*provides the list of output column values and does not control for duplicates similar to SQL Select*/
KEEPFILTERS ( /*does not allow for filter context overrides and will show metric outputs specific to the filter context filtered in the expression*/
FILTER ( /*iterates the table for the specific condition*/
KEEPFILTERS (/*does not allow for filter context overrides and will show metric outputs specific to the filter context filtered in the expression*/
SUMMARIZECOLUMNS ( /*cross joins to output combinations of the attribute values eliminating blanks*/
'Date'[Calendar Year],/*Note there are years without sales*/
'Product'[Category],
"CountRowsSales", CALCULATE (/*overrides filter contexts*/ COUNTROWS ( 'Sales' ) )
)
),/*Table expression for FILTER*/
OR (/*filter component*/
NOT ( ISBLANK ( 'Date'[Calendar Year] ) ),
NOT ( ISBLANK ( 'Product'[Category] ) )/*forcing the output of blanks?*/
)
)
),
"'Date'[Calendar Year]",'Date'[Calendar Year],
"'Product'[Category",'Product'[Category]
),/*TOPN table expression*/
'Date'[Calendar Year],1,/*TOPN order by component ascending = 1*/
'Product'[Category],1
)
The inner SELECTCOLUMNS was able to resolve however for some reason the TOPN is not able to differentiate between the Product categories for each specific year. I do not understand why this query is not functioning. Please advise.
Thank you for your assistance in this matter.
