The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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 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")
I suggest you look at the basket analysis pattern. I have used it and it works well. https://www.daxpatterns.com/basket-analysis/