Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
hrathi38
Regular Visitor

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

3 REPLIES 3
v-yulgu-msft
Microsoft Employee
Microsoft Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
Regular Visitor

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.