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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
spartanboy
Helper II
Helper II

DAX filter and then calculate a measure to reduce processing time

Hello  - 

 

Performing below measure calculation to be used in other measures, matrix and multiple visuals. But, the calculation takes huge amount of time. To accelerate the processing, how can I first filter by a GROUP column and then calculate this measure?

 

calc_cost = sum(F_COST[COST])*IF(SUM(F_COST[QTY])==0,1,SUM(F_COST[QTY]))

 

 

I guess something to do with "CALCULATE" and add some type of filters: ALL SELECTED, KEEP FILTERS. Not sure how to appropriately use them or if this logic is effecient.  Kindly assist with a logical/sample solution 

 

 

 

First attempt:

Tried the below, only to CALCULATE for a Selected GROUP from Slicer. There is no change in the processing time (takes minutes). Please assist with a sample code...

 

Calc_total_cost = CALCULATE(sum(F_COST[COST_PER_UOM])*IF(SUM(F_COST[QTY])==0,1,SUM(F_COST[QTY])),ALLSELECTED(F_COST[GROUP]))

 

For waiting for +5min

spartanboy_0-1658689936409.png

 

@amitchandak 

TIA

1 ACCEPTED SOLUTION

Hi @spartanboy ,

 

Power BI bad performance should be caused by many elements like large size of data, complex data model, complex calculation/query, poor data source response (direct query/live connection) ,too many visuals and so on.

I think your measure is easy. The long duration should not be caused by your code. 

calc_cost = sum(F_COST[COST])*IF(SUM(F_COST[QTY])==0,1,SUM(F_COST[QTY]))

So I think you should optmize Power BI performance by data model.

Here is an offical blog and I hope it could help you. 

For reference: Optimization guide for Power BI

 

Best Regards,
Rico Zhou

 

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

View solution in original post

8 REPLIES 8
lukiz84
Memorable Member
Memorable Member

Can you tell us what you want to achieve?

 

The formulas above don't make much sense without context (your goal?)

 

BR

 

Expectation is to just calc_COST using F_COST[QTY] and  F_COST[COST], and the created measure calc_COST - will be used in other measure ans visuals. 

Hi @spartanboy ,

 

Power BI bad performance should be caused by many elements like large size of data, complex data model, complex calculation/query, poor data source response (direct query/live connection) ,too many visuals and so on.

I think your measure is easy. The long duration should not be caused by your code. 

calc_cost = sum(F_COST[COST])*IF(SUM(F_COST[QTY])==0,1,SUM(F_COST[QTY]))

So I think you should optmize Power BI performance by data model.

Here is an offical blog and I hope it could help you. 

For reference: Optimization guide for Power BI

 

Best Regards,
Rico Zhou

 

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

YourMeasure = SUMX(F_COST, F_COST[QTY] * F_COST[COST])

 

no need for calculate and stuff.

VijayP
Super User
Super User

@spartanboy  you cannot use IF function inside in the above fashion. What do you want ot achieve!?




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


IF function in the original DAX does work. Expectation is to calc_COST using F_COST[QTY] and  F_COST[COST]

VijayP
Super User
Super User

@spartanboy 

Create Aggregations separately and use them in Calculate , currently you have used them inside calculate as SUM(Column) that must be causing latency!




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Tried the below, only to CALCULATE for a Selected GROUP from Slicer. There is no change in the processing time (takes minutes). Please assist with a sample code... TIA

 

 

 Calc_total_cost = CALCULATE(sum(F_COST[COST_PER_UOM])*IF(SUM(F_COST[QTY])==0,1,SUM(F_COST[QTY])),ALLSELECTED(F_COST[GROUP]))

 


For waiting for +5min

spartanboy_0-1658676460764.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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