Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX Calculate if statement

Hi, I am very new to DAX.

I have a sales table that contains the list of customers, invoices and product purchased ( daily). I want a measure that counts the number of unique customers ( customerID) who have purchased more than two units of product X in a month.

 

The problem is that any customer can have multiple purchases in a month hence if a customer has purchased one unit of product X in the first week and one unit in second week then he should be counted.

 

Need Help
  • v-xicai's avatar
    v-xicai
    7 years ago

    Hi Anonymous ,

     

    You can try to create measure like DAX below.

     

    Measure1 = var sum_quantity=CALCULATE(SUM(Sales[Quantity]),FILTER(ALLSELECTED(Sales), Sales[Customer_ID] =MAX(Sales[Customer_ID])&& MONTH(Sales[Sno]) =MONTH(MAX(Sales[Sno])) &&Sales[product] =MAX(Sales[product])))

    return CALCULATE(COUNT(Sales[Customer_ID]),FILTER(ALLSELECTED(Sales),sum_quantity>2 ))

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi Anonymous ,

     

    You can try to create measure like DAX below.

     

    Measure1 = var sum_quantity=CALCULATE(SUM(Sales[Quantity]),FILTER(ALLSELECTED(Sales), Sales[Customer_ID] =MAX(Sales[Customer_ID])&& MONTH(Sales[Sno]) =MONTH(MAX(Sales[Sno])) &&Sales[product] =MAX(Sales[product])))

    return CALCULATE(COUNT(Sales[Customer_ID]),FILTER(ALLSELECTED(Sales),sum_quantity>2 ))

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi  Anonymous ,

     

    Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

     

    Best regards

    Amy

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I am getting wrong answer with this code. The only workaround I have found is that I make a pivot table in which I put product code in Filter, Distributors in Rows, Customer Id and Quantity in Value section. Then I put SUM operation on Quantity and DISTINCTCOUNT operation on Customer ID. Then i choose Value Filter as 'Greater than or equal to' =2. Now the pivot table give me the distint count of Customers that have purchased 2 or more than 2 Product 'X' from me.