How do i optimize this DAX to blazing fast?

[sathishsam]

I have a very simple DAX funtion as below which takes 576 MS to compute. How do i make it even better

CALCULATE (

                SUM (Table1[SumCol] ),

                Table1[Date] = MAX ( Table1[Date] )

                    && Table1[ColA] = 1

                    && Table1[ColB] = 2

                    && Table1[ColC]="A"

                    && Table1[ID1] = SELECTEDVALUE(Table2[ID1])

                    && Table1[ID2] = SELECTEDVALUE(Table2[ID2])

            ),

Table1 has around 2 Million rows and 30 Columns

[danextian]

I don't have a sample model to test this one but try this:

=
CALCULATE (
    SUM ( Table1[SumCol] ),
    Table1[Date] = MAX ( Table1[Date] ),
    Table1[ColA] = 1,
    Table1[ColB] = 2,
    Table1[ColC] = "A",
    Table1[ID1] = SELECTEDVALUE ( Table2[ID1] ),
    Table1[ID2] = SELECTEDVALUE ( Table2[ID2] )
)

This articles goes  into the detail of optimizing multiple filters within CALCULATE.

https://www.sqlbi.com/articles/specifying-multiple-filter-conditions-in-calculate/ 

[sathishsam]

Its the same formula that i have currently. What is the change in your suggetion?

[danextian]

Each filterargument has its own column. Yours used &&.  The formula I suggested doesnt.

[Anonymous]

Hi @sathishsam,

You can try to use the following measure formula if it helps with your scenario:

formula =
VAR preFiltered =
    CALCULATETABLE (
        Table1,
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[Date] = MAX ( Table1[Date] )
                && Table1[ColA] = 1
                && Table1[ColB] = 2
                && Table1[ColC] = "A"
        )
    )
VAR idList =
    INTERSECT ( VALUES ( Table2[ID1] ), VALUES ( Table2[ID2] ) )
RETURN
    SUMX ( FILTER ( preFiltered, [ID1] IN idList ), [SumCol] )

In addition, you can also take a look at following document about optimize the performance:

Improve Power BI Performance by Optimizing your DAX | by MAQ Software | MAQ Software | Medium

Regards,
Xiaoxin Sheng

[sathishsam]

Thank you for the time. 

I tried your suggetions and it didn't help much, it increased the timings. See below