topic Re: Need help with optimization of calculated column in DAX Commands and Tips

Need help with optimization of calculated column

SanchezDK — Wed, 03 Jul 2019 07:51:41 GMT

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?

Re: Need help with optimization of calculated column

Anonymous — Wed, 03 Jul 2019 08:17:59 GMT

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

Re: Need help with optimization of calculated column

SanchezDK — Wed, 03 Jul 2019 08:45:08 GMT

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?

Re: Need help with optimization of calculated column

Anonymous — Wed, 03 Jul 2019 08:49:16 GMT

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

Re: Need help with optimization of calculated column

Anonymous — Wed, 03 Jul 2019 08:52:15 GMT

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

Re: Need help with optimization of calculated column

SanchezDK — Wed, 03 Jul 2019 08:54:36 GMT

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.

Re: Need help with optimization of calculated column

Anonymous — Wed, 03 Jul 2019 08:56:52 GMT

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

Re: Need help with optimization of calculated column

SanchezDK — Wed, 03 Jul 2019 09:20:40 GMT

No luck so far... :-)

But thanks for your inputs.

Re: Need help with optimization of calculated column

Anonymous — Wed, 03 Jul 2019 09:22:56 GMT

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