Forum Discussion
Error with bar chart visual SummarizeColumns() and AddMissingItems() may not be used in this context
I have a list of companies. The companies have products categorized by product code. What I'm trying to do is select a company (or multiple) in a slicer and see the count of products that other companies have in the same product code as the selected company (or multiple). The below DAX works great for that in a simple card, but if I use it in a barchart for example so I can visualize the count by product code, then I get the error SummarizeColumns() and AddMissingItems() may not be used in this context. Please help!
Measure =
CALCULATE(
DISTINCTCOUNT('TableA'[productid]),
SUMMARIZECOLUMNS('TableA'[product_code],
FILTER('TableA','TableA'[company_name] IN VALUES('TableB'[company_name]))))
hi Anonymous
For your requirment, you could try these two measure:
including = CALCULATE(COUNTA(TableA[ProductID]),FILTER(ALL(TableA), TableA[Product Code] IN VALUES(TableA[Product Code])))not including = CALCULATE(COUNTA(TableA[ProductID]),FILTER(ALL(TableA), TableA[Product Code] IN VALUES(TableA[Product Code])))-CALCULATE(COUNTA(TableA[ProductID]))Result:
and here is sample pbix file, please try it.
Regards,
Lin
3 Replies
Anonymous , what are you trying to achieve using SUMMARIZECOLUMNS?
Try like
Measure =
CALCULATE(DISTINCTCOUNT('TableA'[productid]),
FILTER('TableA','TableA'[company_name] IN VALUES('TableB'[company_name])))
amitchandak I'm not trying to just count the unique productid's for a given company. I'm trying to count the unique productid's that match the product_code's of the given company.
Example Data Below. I want to select Acme in a slicer, and then count how many unique product id's match the product codes of Acme. So Acme's product codes are AA and BB, so the count of unique product id's that match those product codes is 4 (including Acme) or 2 (not including Acme)
ProductID Product Code Company 12 AA Acme 13 AA Globex 42 BB Acme 11 BB Initech 121 CC Initech hi Anonymous
For your requirment, you could try these two measure:
including = CALCULATE(COUNTA(TableA[ProductID]),FILTER(ALL(TableA), TableA[Product Code] IN VALUES(TableA[Product Code])))not including = CALCULATE(COUNTA(TableA[ProductID]),FILTER(ALL(TableA), TableA[Product Code] IN VALUES(TableA[Product Code])))-CALCULATE(COUNTA(TableA[ProductID]))Result:
and here is sample pbix file, please try it.
Regards,
Lin
