DAX too Slow with Filtering

[zazaalaza]

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!

[zazaalaza]

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

[AlexisOlson]

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
        )
    )

[AlexisOlson]

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
    )

[zazaalaza]

Thanks but that doesn't help.