Counting where it doesn't exist

[Sha]

I am struggling to find the solution: I have data in 1 row (company, product). I am wanting to choose a product and show all companies that have NOT purchased this product. I also want to see 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 Required: I want to be able to select a product (Product 1) and see the list of companies that could still sell Product 1:

Company C

Company D

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

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

[Anonymous]

HI @Sha,

If you want to use slicer works with a 'selector' instead of a filter, please use unrelated table fields as the source of the slicer or they will filter related records first. (you can extract the product value to create a new table)

Selector= VALUES(Table[Product])

#1. It seems like a common invert selection requirement, I think you can use 'INTERSECT' function to compare with the selected products and all table products if they contain intersect values. (write a measure to compare current aggregated value and selection value and return tag then use on the visual level filter to filter records)

Measure filter =
VAR list =
    INTERSECT ( ALLSELECTED ( Selector[Product] ), VALUES ( Table[Product] ) )
RETURN
    IF ( COUNTROWS ( list ) = 0, "Y", "N")

#2. You can add more condition to filter records who has less than two products in current aggregate row contents.

Selection not include & product > 2 =
VAR list =
    INTERSECT ( ALLSELECTED ( Selector[Product] ), VALUES ( Table[Product] ) )
RETURN
    IF (
        COUNTROWS ( list ) = 0
            && COUNTROWS ( VALUES ( Table[Product] ) ) >= 2,
        "Y",
        "N"
    )

Notice: these measure filters can works with a table visual with Company as the category, count of the product as value.

If these not help, can you please share some dummy data with a similar data structure to test and coding formula? It is hard to provide the sample formula without any default data.

How to Get Your Question Answered Quickly 
Regards,

Xiaoxin Sheng

[Sha]

Thank you, that gets me pretty close.  I'm now trying to figure out how to get the count of companies that have no product so I can do calculation with.  (I see it in the table but once I remove the Client it goes back to total).  I've got a pbi file, but haven't figured out how to upload for you.

top left visual: Matrix... Rows=Company[Company], Column=Data[Product], Values=Data[Product cnt]

top right visual: Filter... Field=Product[Product]

bottom left visual: Table... Values= Company[Company], Data[Product]

bottom middle visual: Table...Values=Company[Company], DistinctCount of Company[Company] Filter on Visual is Measure=Y

bottom right visual: I want this to be same count as bottom middle visual

Measures used:

Product cnt = count(Data[Product]) +0   

Measure =

var list =

INTERSECT(ALLSELECTED(Products[Product]),VALUES(Data[Product]))

RETURN

if(

COUNTROWS(list) = 0

&& COUNTROWS(VALUES(Data[Product]) ) >=2,

"Y",

"N")

 

Tables:

Products = Distinct(Data[Product])

Company = DISTINCT(Data[Company])

DATA:

Company	product
a	table
a	chair
a	phone
b	chair
c	chair
c	phone
d	chair
d	phone

[Anonymous]

HI @Sha,

Maybe you can try this measure, it will count the company which not have selected product:

Not existed Company count =
CALCULATE (
    COUNTROWS ( VALUES ( data[Company] ) ) + 0,
    FILTER ( ALLSELECTED ( data ), NOT ( [Product] IN VALUE ( Product[Product] ) ) )
)

Regards,

Xiaoxin Sheng

[Sha]

correction:

bottom middle visual: Table...Values=Company[Company], DistinctCount of Data[Company]

[Sha]

@Anonymous 

I'm still looking for help on how to isolate the count to use in a calculation.

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