Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Distinct count with multiple conditions not working

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 🙂
 
Need Help

1 Reply

  • ibarrau's avatar
    ibarrau
    Super User
    4 years ago

    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.