DAX Improvement

[hrathi38]

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

[v-yulgu-msft]

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

[hrathi38]

Hi @v-yulgu-msft,

I completely agree to the points that you've mentioned, but these are the measures that I've created in SSAS Tabular model designer, which I suppose can function a lot faster to process a million of records as compared to Power BI.

Also, I'm already using Direct Query mode to connect data. And regarding the variables, I think I can surely check on that.

Thanks you so much!

Harshey

[hrathi38]

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