Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Total order lines with spefic products

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 IdProduct Name

1

PC
1PC
1Tablet
1Tablet
1Tablet Charger
2Tablet
2Tablet Charger
2Tablet Charger
3PC
3Tablet Charger

 

Result

Customer Order Line IdProduct NameTablet CountTablet Charger Count
1Tablet21
1Tablet Charger21
2Tablet12
2Tablet Charger12
1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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")

 

 

 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

@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
Community Champion
Community Champion

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.