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

5 Replies

  • 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.

  • rodrigosan's avatar
    rodrigosan
    Responsive Resident
    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
    )
)
    • STEVE_WT's avatar
      STEVE_WT
      Frequent Visitor
      9 months ago

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    9 months ago

    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.

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    9 months ago

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