Forum Discussion
Counting where it doesn't exist
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.
6 Replies
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))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
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=Ybottom right visual: I want this to be same count as bottom middle visualMeasures used:Product cnt = count(Data[Product]) +0Measure =var list =INTERSECT(ALLSELECTED(Products[Product]),VALUES(Data[Product]))RETURNif(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 correction:
bottom middle visual: Table...Values=Company[Company], DistinctCount of Data[Company]
