Help with a measure DAX

[STEVE_WT]

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 category and this works fine.

I need another formula similar to this but to only count customers who have bought from more than one product category where a certain product category features e.g. apples.

My current formula  :

Plus One Customers = CALCULATE( DISTINCTCOUNT('ALL DATA MART'[Master Account No]), FILTER( VALUES('ALL DATA MART'[Master Account No]), CALCULATE(DISTINCTCOUNT('ALL DATA MART'[Product  Category])) > 1 ) )

So I just need this count of customers buying more than one product category but where one of them is 'apples'.

Any help would be appreciated.

Thanks

[ryan_mayu]

@STEVE_WT 

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
)
)

[Ashish_Mathur]

Hi,

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.

[Anonymous]

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.

[rodrigosan · Accepted solution]

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_WT]

Perfect. Thanks for breaking down the expanation of the formula also.