- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Power Platform Integration - Better Together!
- Power Platform Integrations (Read-only)
- Power Platform and Dynamics 365 Integrations (Read-only)
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Better way to write this DAX measure ?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Better way to write this DAX measure ?

04-30-2020
01:44 PM

Hi Guys

is there a better way to write this DAX query ?

This measure is taking for ever to populate data as i have over 5 million records but gives me correct data..For users its timing out after 30 min.

Totally Ms it took when checking with performance analyser - 250000ms

I couldnt thinkg of any other way to write it yet.

This measure is developed to calculate the gross margin attaintment% for 4 different scenarios .

Quoted GM -Ve and Actual GM +Ve

Quoted GM +Ve and Actual GM -ve

Quoted GM +Ve and Actual GM +Ve

Quoted GM -Ve and Actual GM +ve

sample data:

QotedGM | ActualGM | GM attainment% - Correct value |

-4189 | -530 | 12.65% |

566 | 505 | 89.22% |

-129537 | -44370 | 34.25% |

744 | 340 | 45.70% |

-104 | 244 | 334.62% |

5695 | -629 | -111.04 |

I seperated them as three different measures and tried to sum them , still it is slow.

Data source is analysis server tabular model and LIVE connection.

Pls help.thanks

Measure =

CALCULATE (

DIVIDE (

SUM ( table[actual_margin] )

- SUM ( table[[quoted_margin]] ),

SUM ( table[[quoted_margin]] ),

0

) * -1,

FILTER (

table,

SUM ( table[quoted_margin] ) < 0

),

FILTER(

table,

SUM ( table[actual_margin] ) <> BLANK())

, FILTER(

table,

SUM ( table[actual_margin] ) > 0

))

+

CALCULATE (

DIVIDE (

SUM ( table[actual_margin] )

- SUM ( table[quoted_margin] ),

SUM ( table[quoted_margin] ),

0

),

FILTER (

table,

SUM ( table[quoted_margin] ) > 0

),

FILTER (

table,

SUM ( table[actual_margin] ) < 0

)

)

+

CALCULATE (

DIVIDE (

SUM ( table[actual_margin] ),

SUM ( table[quoted_margin] ),

0

),

FILTER (

table,

SUM ( table[quoted_margin] ) > 0

),

FILTER (

table,

SUM ( table[actual_margin] ) > 0

)

)

+

CALCULATE (

DIVIDE (

SUM ( table[actual_margin] ),

SUM ( table[quoted_margin] ),

0

),

FILTER (

table,

SUM ( table[quoted_margin] ) < 0

),

FILTER (

table,

SUM ( table[actual_margin] ) < 0

)

)

I tried to use variables and implement the same measure, though it was very fast but margin attaintment doubles up and its not correct.

Below is the way i wrote the measure with variable which is getting doubled.

I think i am doing something wrong with the varialb implementation.

Measure =

var S_agm = sum(table[actual_margin])

var s_QGM = sum(table[quoted_margin)

CALCULATE (

DIVIDE (

S_AGM

- SUM ( table[[quoted_margin]] ),

SUM ( table[[quoted_margin]] ),

0

) * -1,

FILTER (

table,

S_QGM < 0

),

FILTER(

table,

S_AGM <> BLANK())

, FILTER(

table,

S_AGM > 0

))

+

CALCULATE (

DIVIDE (

S_AGM

- S_QGM,

S_QGM,

0

),

FILTER (

table,

S_QGM > 0

),

FILTER (

table,

S_AGM < 0

)

)

+

CALCULATE (

DIVIDE (

S_AGM,

S_QGM,

0

),

FILTER (

table,

S_QGM > 0

),

FILTER (

table,

S_AGM > 0

)

)

+

CALCULATE (

DIVIDE (

S_AGM,

S_QGM,

0

),

FILTER (

table,

S_QGM < 0

),

FILTER (

table,

S_AGM < 0

)

)

thanks

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-02-2020
02:14 PM

A measure itself does not take space. The calculation, on the other hand, may need a lot of space if it's not optimized correctly and a lot of data needs to be materialized. But your formula is simple and should run very quickly. I suspect I don't know all the details because you've not disclosed everything about your model.

First off, you should make sure that your fact table uses only columns that you really need and that they have the correct data types. This also means you have to decide if your decimals must have the number of decimal places you are having right now or if they can be rounded to, say, 2 decimal places. This has a huge impact on the memory footprint of the table.

Also, you should have a proper design, which means "star-schema." If you don't, then you'd better start creating it.

Secondly, you are saying you are using a Live Connection. I understand this is a connection to a tabular cube? If not, and you are simply using a sql database, then you'd better optimize your structures. First of all, you should have indexes on your tables, most likely you need a columnstore index on your fact table. I don't know what your system is, so can tell you only about SQL Server since this is what I'm expert at.

This measure (which is your measure) should be blazingly fast:

```
[Measure] =
var __actualMargin = SUM ( table[actual_margin] )
var __quotedMargin = SUM ( table[quoted_margin] )
var __result =
switch( true(),
( __quotedMargin * __actualMargin ) < 0,
DIVIDE(
sign( __quotedMargin ) * ( __actualMargin - __quotedMargin ),
__quotedMargin
),
( __quotedMargin * __actualMargin ) > 0,
DIVIDE(
__actualMargin,
__quotedMargin
)
)
return
__result
```

If it's not, then something's really wrong with your model or your computer is just very weak.

Best

D

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

15 REPLIES 15

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-01-2020
01:31 AM

hi @Anonymous

You may try to Use variables to improve your formulas. This should improve your perforence:

https://docs.microsoft.com/en-us/power-bi/guidance/dax-variables#improve-performance

Regards,

Lin

Community Support Team _ Lin

If this post**helps**, then please consider *Accept it as the solution** to help the other members find it more quickly.*

If this post

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-01-2020
05:09 AM

hi @v-lili6-msft ,

I agree , but i am not sure where i need to put the variables as when i created the measure with varibable , the values are doubling up .

Nopt sure how to fix it

thanks

V

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-01-2020
10:48 AM

Please take a good book on DAX or enroll in an online course and start your journey. There's a lot you've got to discover and learn about the language, so do not waste time.

And here's something that seems like the formulation you want:

```
[Measure] =
// This is the first part of your measure...
var __actualMargin = SUM ( table[actual_margin] )
var __quotedMargin = SUM ( table[quoted_margin] )
var __shouldCalc = __quotedMargin < 0 && __actualMargin > 0
return
if( __shouldCalc,
DIVIDE(
(-1) * ( __actualMargin - __quotedMargin ),
__quotedMargin
)
)
+
// Change the other parts accordingly.
...
```

Your filters filter either the full 'table' or return an empty one because when you calculate the SUM under them, there's no context transition. So, that means you can just use a simple logical expression as I have above.

Best

D

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-02-2020
12:23 PM

Hi @Anonymous,

Thanks for the feedback

Yeah i know i am being silly at some basics.

Thank you for replying. I tried with the sample formula you suggested and I improvised it .Below is the logic I created with your suggestion.However when i run this query it never got populated any reults .Its timing out for some reason . Its taking for every to load and never got loaded.

I think i am doing something wrong. Can you pls help me with this ?

GM%% =

VAR A_GM =

SUM ( FACT_DEALS_DPT[End Customer Gross Margin USD] )

VAR Q_GM =

SUM ( FACT_DEALS_DPT[Quoted Gross Margin US Dollar Amount] )

VAR Diff = A_GM - Q_GM

VAR case1 = A_GM > 0 && Q_GM < 0

VAR case2 = A_GM < 0 && Q_GM > 0

VAR case3 = A_GM > 0 && Q_GM > 0

VAR case4 = A_GM < 0 && Q_GM < 0

RETURN

IF (

case1,(-1)*

DIVIDE ( ( Diff ), Q_GM, 0 ),

IF (

case2,

DIVIDE ( Diff, Q_GM, 0 ),

IF (

OR( case3,case4),

DIVIDE(A_GM,Q_GM,0)

----DIVIDE ( A_GM, Q_GM, 0 ), IF ( case4, DIVIDE ( A_GM, Q_GM, 0 ) ) )---

)

))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-02-2020
12:33 PM

I don't understand the terminology you use. Please be precise.

1. A measure is a formula that is calculated on demand from the underlying data and responds to slicing and dicing.

2. A query is something you start with EVALUATE.

3. A calculated column is a column in one of the model tables and is calculated only when data is loaded or refreshed.

Also, since the cases in your formula are mutually exclusive and there are more then 2, you could/should use SWITCH, not IF, for clarity.

If you want this calculation to be performed in a calculated column in a fact table that has tens of millions of rows... well, you're a bit out of luck unless you have a very powerful server. Also, please bear in mind that calculated columns on big fact tables are a VERY BAD IDEA. Fact tables should already have all columns prepared in the data source. Always.

Best

D

1. A measure is a formula that is calculated on demand from the underlying data and responds to slicing and dicing.

2. A query is something you start with EVALUATE.

3. A calculated column is a column in one of the model tables and is calculated only when data is loaded or refreshed.

Also, since the cases in your formula are mutually exclusive and there are more then 2, you could/should use SWITCH, not IF, for clarity.

If you want this calculation to be performed in a calculated column in a fact table that has tens of millions of rows... well, you're a bit out of luck unless you have a very powerful server. Also, please bear in mind that calculated columns on big fact tables are a VERY BAD IDEA. Fact tables should already have all columns prepared in the data source. Always.

Best

D

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-02-2020
01:00 PM

Hi @Anonymous Apologies for the confusion .

I am creating a measure and not a formula .Apologies again if that confused you.

I used IF function since you replied me in your DAX with if function .

I wonder if SWITCH will work in my case ...Since the data in fact table are huge , I think i may get "No memory error" too.

Do you think is it a better idea to write the logic in calculated column in DB .

I have a 64gb memory on server.

May i know your suggestion?

My total rows in my facttable till now are 7607510 (7 Million)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-02-2020
02:14 PM

A measure itself does not take space. The calculation, on the other hand, may need a lot of space if it's not optimized correctly and a lot of data needs to be materialized. But your formula is simple and should run very quickly. I suspect I don't know all the details because you've not disclosed everything about your model.

First off, you should make sure that your fact table uses only columns that you really need and that they have the correct data types. This also means you have to decide if your decimals must have the number of decimal places you are having right now or if they can be rounded to, say, 2 decimal places. This has a huge impact on the memory footprint of the table.

Also, you should have a proper design, which means "star-schema." If you don't, then you'd better start creating it.

Secondly, you are saying you are using a Live Connection. I understand this is a connection to a tabular cube? If not, and you are simply using a sql database, then you'd better optimize your structures. First of all, you should have indexes on your tables, most likely you need a columnstore index on your fact table. I don't know what your system is, so can tell you only about SQL Server since this is what I'm expert at.

This measure (which is your measure) should be blazingly fast:

```
[Measure] =
var __actualMargin = SUM ( table[actual_margin] )
var __quotedMargin = SUM ( table[quoted_margin] )
var __result =
switch( true(),
( __quotedMargin * __actualMargin ) < 0,
DIVIDE(
sign( __quotedMargin ) * ( __actualMargin - __quotedMargin ),
__quotedMargin
),
( __quotedMargin * __actualMargin ) > 0,
DIVIDE(
__actualMargin,
__quotedMargin
)
)
return
__result
```

If it's not, then something's really wrong with your model or your computer is just very weak.

Best

D

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-02-2020
03:20 PM

Hi @Anonymous

Not sure whats going wrong.

I just modifed the query you showed me and even that is very slow for me.

Not sure whats the real cause.You said this meaure will be so FAST then i guess it might be . May be my machine is all exhasted after multtiples tries.Let me give a try tomorrow and see if that works fast

I have Snowflake schema .

I tried removing this measure from the table in power bi and the data populate so fast .When i try to add it agin , it becomes so slow .

I have a big matrix visual with 10 columns and 18 values(or metrics).May be this could a reason too. I dont know.

.Let me also try any ways that comes to my mind .

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-06-2020
10:56 AM

Hi @Anonymous ,

I found the issue .The issue was that i had around 12 Rows and 12 value created in the matrix.I have also added Sub totals in few levels like the customer level , region level and ID level. Since it has to calculate multiple millions of reocrds it got slowed now.

I collapsed the matrix tables , limited my filters and then expanded VOILA it was so fast .

Thanks a lot for your help

All the Best

V

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-06-2020
01:25 PM

Great.

Best

D

Best

D

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-01-2020
10:27 AM

Variables in functional languages are IMMUTABLE. Once they are assigned, they cannot change, therefore using them in filters the way you do is pointless.

Best

D

Best

D

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-30-2020
02:25 PM

Please use Power Query. DAX is not a tool for populating fact tables.

Best

D

Best

D

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-01-2020
05:06 AM

@Anonymous I am using Live connection .Not sure if i can use power query ..Can i ?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-30-2020
02:15 PM

Hi @Anonymous

Can you provide a sample and explain what you are trying to calculate?

Best Regards,

Mariusz

If this post**helps**, then please consider *Accepting it as the solution*.

Please feel free to connect with me.

LinkedIn

Mariusz

If this post

Please feel free to connect with me.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-01-2020
05:02 AM

Announcements

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

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

Featured Topics

Top Solution Authors

User | Count |
---|---|

118 | |

99 | |

69 | |

67 | |

43 |

Top Kudoed Authors

User | Count |
---|---|

153 | |

105 | |

103 | |

87 | |

64 |