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 ) )
Ashish_Mathur
Super User
9 months agoHi,
Try these DAX measure pattern
Total = DISTINCTCOUNT('ALL DATA MART'[Product Category])
Apples count = calulate([Total],'ALL DATA MART'[Product Category]="Apples")
Measure = calculate(distinctcount('ALL DATA MART'[Master Account No]),filter(VALUES('ALL DATA MART'[Master Account No]),[Total]>1&&[Apples count]>0))
Hope this helps.