Need help with optimization of calculated column

[SanchezDK]

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?

[Anonymous]

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]

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]

By the way, if you create the relationship, then you can use this (the fastest option with DAX):

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

Best

Darek

[SanchezDK]

Sounds great. However right now I am struggling with the creation of a key to map the entry no with the number on G/L Register that represents the relevant range.

[Anonymous]

You should do it in PowerQuery and I assume this is what you do. Certainly, there are myriads of articles on the net about how to do this as it must be a very common thing to do in PQ. Just a matter of a good search...

Best
Darek

[SanchezDK]

No luck so far... 🙂 

But thanks for your inputs.

[Anonymous]

If in trouble, please find the relevant forum on PowerQuery and post your question. You can also try people like Chris Webb, Marco Russo, Matt Arlington...

Best

Darek

[Anonymous]

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