Need to count common supplier for more than 3 products

[rob_vander]

Hi All,

I have below dataset. I want to find out count of Orders or list of orders where at least 3 distinct products have one common supplier. In this case Order count will be 2 (10191,102452). I have highlighted rows which satisfies the condition

OrderID,ProductID,SupplierID

10191, 00011, 1088
10191, 00011, 1212
10191, 00011, 1774
10191, 00011,3219
10191, 00013, 1212
10191, 00013,3219
10191, 00033, 1001
10191, 00033,3219
10191, 00032, 0061
102452, 00013, 1212
102452, 00013, 3219
102452, 00027, 3219
102452, 00033, 3219
10297, 00033, 3219
103067, 00033, 1001
103067, 00033, 3219

Note:  I am looking for front end solution first. If it is complex or not possible I will look to do it in script

[Chanty4u]

Try this 

Add OrderID as a dimension

Add the following expression as a measure to count the distinct products per order.

Count(DISTINCT {<ProductID={*}>}

ProductID)

In expression bg color use below exp

If(Count(DISTINCT SupplierID)>2, LightGreen(), White())

[Gabbar · Accepted solution]

For the Front end try this as a measure:-
Count({<OrderID={"=(Count(distinct ProductID)>=3)"},SupplierID={"=(Count(distinct ProductID)>=3)"}>}distinct OrderID)

and for script you will need to self join the table by applying relevant aggregation functions.

[rob_vander]

@Gabbar  thanks