Forum Discussion

STEVE_WT's avatar
STEVE_WT
Frequent Visitor
9 months ago
Solved

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...
Need Help
  • rodrigosan's avatar
    rodrigosan
    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
    )
)
Anonymous's avatar
Anonymous
Not applicable
9 months ago

Hi STEVE_WT ,

Try below Measure.

 

Plus One Customers with Apples =

VAR Customers_MultiCat =

    CALCULATETABLE(

        VALUES('ALL DATA MART'[Master Account No]),

        FILTER(

            VALUES('ALL DATA MART'[Master Account No]),

            CALCULATE(DISTINCTCOUNT('ALL DATA MART'[Product Category])) > 1

        )

    )

 

VAR Customers_Apples =

    CALCULATETABLE(

        VALUES('ALL DATA MART'[Master Account No]),

        'ALL DATA MART'[Product Category] = "apples"

    )

 

RETURN

COUNTROWS(

    INTERSECT(Customers_MultiCat, C

ustomers_Apples)

)

If my response as resolved your issue please mark it as solution and give kudos.