Forum Discussion

zazaalaza's avatar
zazaalaza
Frequent Visitor
2 years ago

DAX too Slow with Filtering

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!

all
Date
DAX
Filter
slow

4 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    2 years ago

    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
    )
    • AlexisOlson's avatar
      AlexisOlson
      Super User
      2 years ago

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