Count where doesn't exist

[Sha]

I'm struggling to find solution:  I have data on 1 row (company, product).  I am wanting to pick a product and show all the companies that have NOT bought this product.  I also only want to see the companies that have sold 2 or more products.

Data:

Company A   Product 1

Company A  Product 2

Company A  Product 3

Company B  Product 2

Company C  Product 2

Company C Product 3

Company D  Product 2

Company D Product 3

Result needed:  I want to be able to select a product ( Product 1)  and see list of companies that could still be sold Product 1:

Company C

Company D

I don't want to see Company B because it hasn't been sold 2 or more products.

I then would also like to count how many companies that I could sell Product 1 to, which would be 2.

[v-chuncz-msft · Accepted solution]

@Sha 

You may take a look at the posts below.

• https://community.powerbi.com/t5/Desktop/Tricky-Slicer-Options/m-p/573381#M270846
• https://community.powerbi.com/t5/Desktop/How-to-exclude-table-rows-from-a-Slicer/m-p/311185#M137871
• https://community.powerbi.com/t5/Desktop/How-to-count-distinct-values-where-its-occurence-count-is/m...

[amitchandak]

@Sha ,

One way to get if you have company Dimension

Except(Company[Company],table[Company])

Other is create a measure and then use nest two measures for your two requirements
measure = count(Table[Product])+0

Filter measure =calculate(sumx(value(company[company]),[measure]),filter(Table,[measure]=0))

Filter measure 2 =calculate(sumx(value(company[company]),[measure]),filter(Table,[measure]>=2))

[Sha]

I created 2 measures but Value didn't work in 2nd measure, should I be using Values?  It didn't produce any results.

Product cnt = count(Sheet1[Product])+0  (when I created matrix, 0 didn't show up)

Company Cnt = CALCULATE(SUMX(values(Sheet1[Company]),[Product cnt]),FILTER(Sheet1,[Product cnt]=0)) (when I created table for Company, Company Cnt  nothing showed up)