DAX Calculate if statement

[Anonymous]

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.

[v-xicai]

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]

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.

[v-xicai · Accepted solution]

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.