Forum Discussion
STEVE_WT
9 months agoFrequent Visitor
Help with a measure DAX
Hi I have a simple transaction table with accounts and product categories. I have a DAX formula already to count the number of customers where they have bought from more than one product cate...
- 9 months ago
Hi, STEVE_WT , see if this helps you.
Apples Plus One Customers = CALCULATE( // 1. COUNT: Count the unique accounts that meet the filtering criteria. DISTINCTCOUNT('ALL DATA MART'[Master Account No]), // 2. FILTER CONTEXT: Iterate through every unique Master Account No. FILTER( VALUES('ALL DATA MART'[Master Account No]), // --- CHECK A: Must have purchased 'Apples' (Filter 1) --- CALCULATE( COUNTROWS('ALL DATA MART'), 'ALL DATA MART'[Product Category] = "Apples" ) > 0 // --- CHECK B: Must have purchased MORE THAN ONE category (Filter 2) --- && CALCULATE( DISTINCTCOUNT('ALL DATA MART'[Product Category]) ) > 1 ) )
rodrigosan
9 months agoResponsive Resident
Hi, STEVE_WT , see if this helps you.
Apples Plus One Customers =
CALCULATE(
// 1. COUNT: Count the unique accounts that meet the filtering criteria.
DISTINCTCOUNT('ALL DATA MART'[Master Account No]),
// 2. FILTER CONTEXT: Iterate through every unique Master Account No.
FILTER(
VALUES('ALL DATA MART'[Master Account No]),
// --- CHECK A: Must have purchased 'Apples' (Filter 1) ---
CALCULATE(
COUNTROWS('ALL DATA MART'),
'ALL DATA MART'[Product Category] = "Apples"
) > 0
// --- CHECK B: Must have purchased MORE THAN ONE category (Filter 2) ---
&&
CALCULATE(
DISTINCTCOUNT('ALL DATA MART'[Product Category])
) > 1
)
)- STEVE_WT9 months agoFrequent Visitor
Perfect. Thanks for breaking down the expanation of the formula also.