Total order lines with spefic products

[Anonymous]

Hi all,

hope someone can help with the following.

I am interested in checking whether I have orders with a specific combination of products. If that is the case then I would like to filter my visual to show these orders. A constraint is that it should ideally be solved using only measures.

As an example below I have an included the input Source and the result i would like to achieve Result. So in below example I am only interested in orders that contain both Tablet and Tablet Charger. I would like to create a visual on Customer Order Id, Product Name level only including those orders that are in scope.

How could I create measure(s) to achieve this? My idea was to create a [Tablet Count] measure/variable for counting the total number of tablets for the specific order and a [Tablet Charger Count] for counting the total number of tablet chargers for the specific order. Then I could filter on these measures to not be blank; which would return me with the orders in scope.

Customer Order Id 1 and 2 would be shown in the visual since both contain a Tablet and a Tablet charger. Customer Order Id would be filtered out.

Source

Customer Order Id	Product Name
1	PC
1	PC
1	Tablet
1	Tablet
1	Tablet Charger
2	Tablet
2	Tablet Charger
2	Tablet Charger
3	PC
3	Tablet Charger

Result

Customer Order Line Id	Product Name	Tablet Count	Tablet Charger Count
1	Tablet	2	1
1	Tablet Charger	2	1
2	Tablet	1	2
2	Tablet Charger	1	2

[Anonymous · Accepted solution]

@MattAllington thanks for the help. With removefilters and switch I solved the problem.

First two measures for counting the number of items per product that are in scope of the two categories:

# PROD CAT 1 = 
SUMX( 
    FILTER('Customer Order Item', 
    RELATED('Product'[Name]) IN {...,...,...}),
    1
)

# PROD CAT 2 = 
SUMX( 
    FILTER('Customer Order Item', 
    RELATED('Product'[Name]) IN {...,...,...}),
    1
)

Measure to use as visual filter to only include orders having both a product from PROD CAT 1 and PROD CAT 2

Scope = 
VAR _prodcat1 =  IF (calculate([# PROD CAT 1], REMOVEFILTERS('Product'[Name])) > 0 , 1 , blank())
VAR _prodcat2 = IF (calculate([# PROD CAT 2], REMOVEFILTERS('Product'[Name])) > 0 , 1 , blank())
VAR _scope = _prodcat1 + _prodcat2 
RETURN
SWITCH(TRUE(),
ISBLANK([# PROD CAT 1]+[# PROD CAT 2]), BLANK(),
_scope > 1, "In Scope")

[MattAllington]

I suggest you look at the basket analysis pattern. I have used it and it works well. https://www.daxpatterns.com/basket-analysis/