Error with bar chart visual SummarizeColumns() and AddMissingItems() may not be used in this context

[luke6363]

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]))))

[amitchandak]

@luke6363 , 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])))

[luke6363]

@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

[v-lili6-msft · Accepted solution]

hi  @luke6363 

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