DAX Improvement

I was just preparing a few measures that'll be hitting against million of records, thus taking a lot of time. I have two DAX formula's that I've written in measures in SSAS (Tabular). All I seek here is to improve the performance of these two formula so that I can even save a few mili seconds on each, thus saving a lot of time over all.

1) IF (

    HASONEVALUE ( 'TableName'[QL] ) & SUM ( 'TableName'[Count] ) >= 20,

    CALCULATE (ROUND (DIVIDE (SUM ( 'TableName'[WS]), SUM ( 'TableName'[WB] )),2)* 100 + 100,

    'TableName'[TS] = "ABC" ),

    BLANK ()

        )

2) CALCULATE(DISTINCTCOUNT('TableName'[RS]), 'TableName'[WB] > 0)

The goal is to use as little as possible from the Formula Engine and rely more on the Storage Engine, or so the best practices say.

Thanks,

Harshey

Hi hrathi38,

As the query need to fetch million of records which is a large dataset loaded into desktop, this would slow down the formula execution and report generation and data refresh. I would suggest you filter out unnecessary data to improve report performance.

In your scenario, you could use “Direct Query” mode to connect data. Also, make use of Variables in DAX formula might increase the efficency of a measure.

For Formula Engine and Storage Engine, please refer to below links to make better usage of them.

Formula engine and Storage engine in SSAS

optimizing dax expressions involving multiple measures

Best regards,

Yuliana Gu