topic Re: DAX too Slow with Filtering in DAX Commands and Tips

DAX too Slow with Filtering

zazaalaza — Mon, 13 Nov 2023 16:08:27 GMT

Hi,

I have the following measure:

Measure = CALCULATE ( COUNT ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), 'Table'[ValidFromDate] < MAX ( 'Date'[Date] ) && 'Table'[ValidToDate] > MAX ( 'Date'[Date] ) ) )

The two tables (Table, Date) are not connected.

I use this to see "Active" contracts on a specific date. However when I place it in a visual to see it on a daily basis (last 12 months for example), it is way too slow (I have 12 million rows).

Is there a way to optimize this?
Much appreciated!

Re: DAX too Slow with Filtering

AlexisOlson — Mon, 13 Nov 2023 16:17:46 GMT

It might help to make the max date a variable.

Measure = VAR _MaxDate = MAX ( 'Date'[Date] ) RETURN CALCULATE ( COUNT ( 'Table'[Value] ), ALL ( 'Table' ), 'Table'[ValidFromDate] < _MaxDate, 'Table'[ValidToDate] > _MaxDate )

Re: DAX too Slow with Filtering

zazaalaza — Tue, 14 Nov 2023 10:50:41 GMT

Thanks but that doesn't help.

Re: DAX too Slow with Filtering

zazaalaza — Tue, 14 Nov 2023 10:52:03 GMT

Perhaps there is a way to make it faster without using Calculate? Greg_Deckler Any ideas?

Re: DAX too Slow with Filtering

AlexisOlson — Tue, 14 Nov 2023 17:52:47 GMT

Yeah, I have seen this pattern be slow occasionally.

Without CALCULATE can be written fairly similarly.

Measure = VAR _MaxDate = MAX ( 'Date'[Date] ) RETURN COUNTROWS ( FILTER ( ALL ( 'Table' ), 'Table'[ValidFromDate] < _MaxDate && 'Table'[ValidToDate] > _MaxDate ) )