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 ) )
ryan_mayu
9 months agoSuper User
pls try this
Plus One Customers With Apples =
CALCULATE(
DISTINCTCOUNT('ALL DATA MART'[Master Account No]),
FILTER(
VALUES('ALL DATA MART'[Master Account No]),
CALCULATE(DISTINCTCOUNT('ALL DATA MART'[Product Category])) > 1
&&
CALCULATE(
COUNTROWS(
FILTER(
'ALL DATA MART',
'ALL DATA MART'[Product Category] = "apples"
)
)
) > 0
)
)