Distinct count with multiple conditions not working

[Anonymous]

Hi All,

I have a table with order data for the past 5 years.The dataset contains order details including order name,customer ID and total price.

I am now trying to Distinctcount all the orders(by order name) per customer however I only want to count the ones that have total value above 0.So if a customer has 6 orders in total and only 5 of them have value above 0 then I want it to filter that order out and not count it against the customers order.

I tried a couple of Dax syntaxes but none worked,i thought the last one would work but this didnt either.

orders per customer excluding 0 values= CALCULATE(

DISTINCTCOUNT(ORDERS_NEW[NAME]),FILTER('ORDERS_NEW'[EMAIL]) AND (ORDERS_NEW[TOTAL_PRICE]>0)

Has anyone ever come across the same issue.

Any help will be much appreciated 🙂

[ibarrau]

Hi. It looks like your DAX expression syntax is wrong. You might want to try it this way:

orders per customer excluding 0 values = 
CALCULATE(
    DISTINCTCOUNT(ORDERS_NEW[NAME])
    , FILTER( 'ORDERS_NEW', ORDERS_NEW[TOTAL_PRICE] > 0 )
)

FILTER function first argument is the table to iterate checking the condition and the second argument is the condition. 

I hope that works.