Forum Discussion

Sha's avatar
Sha
Icon for Helper II rankHelper II
6 years ago
Solved

Count where doesn't exist

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 c...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
    Sha
    Icon for Helper II rankHelper II
    6 years ago

    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)