Forum Discussion

Adak's avatar
Adak
Frequent Visitor
9 years ago
Solved

Need help optimizing Filters in Calculation

Hello,    I'm having an issue with performance with one of my Calculated Columns.   I have a table similar to example below with more than 6 million rows. I am trying to make a column that count ...
Need Help
  • Oxenskiold's avatar
    Oxenskiold
    9 years ago

    Hi Adak,

     

    could you try this column code out, unfortunately I can't test it myself but it should use a lot less memory and do what you want:

     

    Total Qty =
    SUMX (
    FILTER (
    FILTER (
    Inventtrans;
    Inventtrans[ItemWithOrderID] = EARLIER ( Inventtrans[ItemWithOrderID] )
    );
    Inventtrans[TransactionDate] <= EARLIER ( Inventtrans[TransactionDate] )
    );
    Inventtrans[Qty]
    )

     

    Sorry for interrupting if your problem has already been fixed by Xiaoxin Sheng.

     

    Best regards

    Oxenskiold.

Adak's avatar
Adak
Frequent Visitor
9 years ago

Thanks Anonymous

 

Such measure looks promising. One thing that gets error in my environment is:

 

var currentOrderID = MAX(Inventtrans[ItemWithOrderID])

 

Error says: The function MAX takes an argument that evaluates to numbers or dates and cannot work with values of type String.

 

I can't use Earlier here, in many cases I have used a bit more basic measures so maybe my understanding is a bit limited here, what should I use.

 

Would be grateful if you could explain how can I get rid of this error.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi Adak,

 

You can convert "ItemWithOrderID" column to whole number format.

 

 

Regards,

Xiaoxin Sheng

  • Adak's avatar
    Adak
    Frequent Visitor
    9 years ago

    Hi Anonymous

     

    I'm not sure I understood this correctly, that field already includes some letters so it doesn't allow me to convert that columnt to whole number

     

    Error: Cannot convert value 'Hammer1' of type Text to type Integer.