Forum Discussion

SanchezDK's avatar
SanchezDK
Frequent Visitor
7 years ago

Need help with optimization of calculated column

In my model I have a calculated column used to find the creation date of an entry number on the table 'G/L Entry'. The date is located on the table 'G/L Register'.

 

My formula is a follows:

Creation Date = CALCULATE(
VALUES('G/L Register'[Creation Date]);
FILTER('G/L Register';
'G/L Entry'[Entry No] >= 'G/L Register'[From Entry No_] &&
'G/L Entry'[Entry No] <= 'G/L Register'[To Entry No_]
))

However, this formula is incredibly heavy on memory and performance and takes forever to evaluate. 
Does anybody know how to improve this?

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    What about this:

     

    [Creation Date] = -- column in 'G/L Entry'
var _entryNumber = 'G/L Entry'[Entry No]
return
	CALCULATE(
		MAX( 'G/L Register[Creation Date] ),
		'G/L Register'[From Entry No_] <= __entryNumber,
		__entryNumber <= 'G/L Register'[To Entry No_]
	)

    First, I understand that the intervals [[From Entry No_], [To Entry No_]] are ranges that cover the whole range and are non-overlapping. Second, I understand there's no relationship set up between the two tables. But if there is, for instance, 'G/L Entry' has a many-to-one relationship with 'G/L Register' (as I think it should have since one entry can easily map into just one record in 'G/L Register' that the entry number belongs to), then the measure would be different and potentially much, much faster:

    [Creation Date] = CALCULATE( MAX( 'G/L Register[Creation Date] ) )

     

    Best

    Darek

    • SanchezDK's avatar
      SanchezDK
      Frequent Visitor
      7 years ago

      Hi Anonymous ,

      Thank you for your response. 

      I have tried your first suggestion, however this takes up all memory. 

       

      You are right about your assumptions. There is no relationship between  'G/L Entry' and 'G/L Register'. Perhaps I could create a key to map the Entry No from G/L Entry to the range number that it belongs to in G/L Register?

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Yes, you absolutely should if you can. Calculations based on physical relationships are 10-100x faster than without them.

         

        But you also could create the column in your ETL layer, which would be the best solution possible.

         

        Best
        Darek