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

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.

Reply
Anonymous
Not applicable

Better way to write this DAX measure ?

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:

QotedGMActualGMGM attainment% - Correct value
-4189-53012.65%
56650589.22%
-129537-4437034.25%
74434045.70%
-104244334.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

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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

View solution in original post

Anonymous
Not applicable

15 REPLIES 15
v-lili6-msft
Community Support
Community Support

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.
Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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 ) ) )---
)
))
Anonymous
Not applicable

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
Anonymous
Not applicable

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) 

Anonymous
Not applicable

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

Anonymous
Not applicable

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 .

 

Anonymous
Not applicable

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

Anonymous
Not applicable

Great.

Best
D
Anonymous
Not applicable

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
Anonymous
Not applicable

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

Best
D
Anonymous
Not applicable

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

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

 

Anonymous
Not applicable

hi @Mariusz 

 

I edited my post and include more information.

 

Sample data too provided.

 

Thanks 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors