Hi Everyone, I keep getting errors on my Dax and nothing seems to work so I was wondering if someone has come across something similar. I have a table with all the orders and customer Id's and I am trying to sum the number of orders each customer has by customer ID. Some of the orders are duplicated on the table so I am trying a distinct count. Also, there are orders that have zero value there which I need to exclude as they are exchanges i initially triedcolumn = IF('custom table'[value]=>0,DISTINCTCOUNT('custom table'[order name]),ALLEXCEPT('custom table','custom table'[customer email])) that didnt seem to work and I kept getting numeric error so I then created an extra column tagging all orders above with value above zero as "processed orders" and tried to readjust my syntax but that didnt seem to work either. column = IF('custom table'[Order type]="Processed Order",DISTINCTCOUNT('custom table'[order name]),ALLEXCEPT('custom table','custom table'[customer email])) Any idea on what can I do to fix this? Thank you in advance 🙂

Hey Guys, UPDATE: I managed to get it work 🙂 Its seems that due to size of the rows the calculations werent working well but I created an extra field with an if statement which would class the row (I created the extra column because there were some zero value orders that I needed to keep) and then I filter the data though Power query saved and applied,and did an extra column with the below formula,this sorted my issue. lets see result = CALCULATE(COUNT(ORDERS_NEW[NAME]),ALLEXCEPT(ORDERS_NEW,ORDERS_NEW[EMAIL])) Thank you everyone for your help 🙂

Hi,Wouldn't this make more sense as a measure, instead of a calculated column?Measure:Unique Orders = CALCULATE ( DISTINCTCOUNT ( 'custom table'[order name] ), 'custom table'[value] > 0 )Or, calculated column:Unique_Orders = CALCULATE ( DISTINCTCOUNT ( 'custom table'[order name] ), FILTER ( 'custom table', 'custom table'[customer email] = EARLIER ( 'custom table'[customer email] ) && 'custom table'[value] > 0 ) )Regards

Hi Jos_Woolley , The measure wouldnt work in my case as i want after that calculation i want to sum the number of users that had 10 purchases in their records,14,15,16,17 etc. I tried the calculated column but the syntax seems to get stick working on it for more than 30 min.

How many rows' worth of data are we talking about?Based on what you've said, it might be preferable to do this in Power Query.Regards

Hi Anonymous Power Query won't solve the issue as it shouldn't be used to do Calculations. Your formulas( column = IF...) are not correct. Can you share a Sample of your model? it's better and faster to help you with your issue

Sumifs equivalent Dax not working | Microsoft Fabric Community

12 Replies

Jos_Woolley

Solution Sage

5 years ago

Hi,

Wouldn't this make more sense as a measure, instead of a calculated column?
Measure:

Unique Orders =
CALCULATE (
    DISTINCTCOUNT ( 'custom table'[order name] ),
    'custom table'[value] > 0
)

Or, calculated column:

Unique_Orders =
CALCULATE (
    DISTINCTCOUNT ( 'custom table'[order name] ),
    FILTER (
        'custom table',
        'custom table'[customer email] = EARLIER ( 'custom table'[customer email] )
            && 'custom table'[value] > 0
    )
)

Regards

Anonymous
Not applicable
5 years ago
Hi Jos_Woolley ,

The measure wouldnt work in my case as i want after that calculation i want to sum the number of users that had 10 purchases in their records,14,15,16,17 etc.

I tried the calculated column but the syntax seems to get stick working on it for more than 30 min.
Jos_Woolley
Solution Sage
5 years ago
How many rows' worth of data are we talking about?
Based on what you've said, it might be preferable to do this in Power Query.
Regards
- Anonymous
  Not applicable
  5 years ago
  hi Jos_Woolley,
  
  its around 2.1 million rows.
aj1973
Community Champion
5 years ago
Hi Anonymous
Power Query won't solve the issue as it shouldn't be used to do Calculations. Your formulas( column = IF...) are not correct. Can you share a Sample of your model? it's better and faster to help you with your issue
- Anonymous
  Not applicable
  5 years ago
  Hi,
  I have attached below an excel example of my data and what they look like.
  
  what i want to get frm this calculation that I am trying to do is to be able to get a bar chart showing number of customers that has 5 purchases on theirs records,6,7,10,20 etc.in order to be able to see how many purchases do returning customers tend to have.
Ashish_Mathur
Super User
5 years ago
Hi,

Share some data and show the expected result.
- Anonymous
  Not applicable
  5 years ago
  Hi,
  I have attached below an excel example of my data and what they look like.
  
  what i want to get frm this calculation that I am trying to do is to be able to get a bar chart showing number of customers that has 5 purchases on theirs records,6,7,10,20 etc.in order to be able to see how many purchases do returning customers tend to have.
  - Ashish_Mathur
    Super User
    5 years ago
    Hi,
    
    Share the download link of your PBI file and for a sample sample show the expected result.

Forum Discussion

Sumifs equivalent Dax not working

12 Replies

Related content

SUMIF Equivalent in DAX

sumif not working

SumIf

DAX SUMIFS Equivalent

DAX equivalent for sumif