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.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi Adak,

 

For your dax formula, you can refer to below codes:

 

Measure :

Sum of total = 

var currentOrderID = MAX(Inventtrans[ItemWithOrderID])

var currentTransactionDate= MAX(Inventtrans[TransactionDate])

return

SUMX(FILTER(ALL(Inventtrans),AND(Inventtrans[ItemWithOrderID]=currentOrderID,

                Inventtrans[TransactionDate]<=currentTransactionDate)),Inventtrans[Qty])

 

 

In addition, you can use TopN function to choose the specify number of rows to a new table, then use above formula on new table to see if issue persists.

 

Regards,

Xiaoxin Sheng

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.