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.