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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
emarome94
Helper I
Helper I

Calculate Statement - Slow Table Load with many informations

Hi guys,

I have created this measure (Measure1) that actually obtain the result I want to discover and gives not many problem in limited filter context like table with few rows. 
Measure1=
CALCULATE(

    [Measure0],
    FILTER( 'Accounting', 'Accounting'[Fiscal Period] >= RELATED( 'Price Lists'[Fiscal Period] )
))
The problem comes when I add more information in the table report like customer, item, country etc. lead to a very slow loading. 
The expression within calculate is made by the following measure:
Measure0 =
SUMX
    FILTER ( 'Price Lists', 'Price Lists'[Old Price]> 0 ),
IF (
        [ASP] > [Old Price],
        ( [ASP] - [Old Price] ) * [Quantity],
        BLANK() ))

From the Measure 0 I am creating a context where from the price list I am creating a table with only the values "old price" area greater than zero and then creating and then the if statement.
The measure 1 is using the measure 0 only where is retrieving the fiscal period from the price list with related. 

 

Can someone help me maybe with a best practice to fix the formula?
Really thank you in advance for your help,

Regards,

ES

1 ACCEPTED SOLUTION

Hi,

Now that the issue is total not showing I would recommend checking this article by SQLBI:
Obtaining accurate totals in DAX - SQLBI

In short try utilizing methods such as SUMX to get the total value. 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
ValtteriN
Super User
Super User

Hi,

The issue is likely caused by your use of RELATED and additionally the use of complex logic within IF statement. RELATED function often suffers from performance issues and it should be avoided when possible. In your dax you use related to filter upcoming fiscal periods for calculation:    FILTER( 'Accounting', 'Accounting'[Fiscal Period] >= RELATED( 'Price Lists'[Fiscal Period] )

The first question to ask here is if you can achieve the same calculation without using RELATED e.g. using MAX:

Measure1=
CALCULATE(

    [Measure0],
    FILTER( 'Accounting', 'Accounting'[Fiscal Period] >= MAX( 'Price Lists'[Fiscal Period] )
))


One other thing to consider is if you need FILTER here. often you can achive the same results just by dropping it. 

e.g. 



Measure1=
var _period = MAX( 'Price Lists'[Fiscal Period] )
CALCULATE(

    [Measure0],
   'Accounting'[Fiscal Period] >= _period)

Generally speaking avoid using RELATED and FILTER and your calculations should be faster.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @ValtteriN,

 

Thank you for your quick response.

 

Unfortunately, I can not reach the same result with the MAX measure (in this case it would retrieve the higher fiscal period in the price list fact table) where instead I need the exact fiscal period for start the calculations. 

 

For instance if in the price list table there is the customer "BigCompany" with item "RedItem" with fiscal period 2023004, I need to start my calculation from FY 2023004 ( >= related (price list[fiscalperiod]) )

 

 

I see, maybe you could calculate the desired fiscal year using CALCULATE()? It is hard to provide example fitting your use case without seeign the data, but the basic idea is as follows:

Instead of using RELATED you would use the same filter context in variable
var _FY = 
CALCULATE(MAX( 'accounting period'[Fiscal Period]), ALL(accounting period[Fiscal Period]), 'accounting period'[Fiscal Period] =MAX( 'Price Lists'[Fiscal Period]))
return 

CALCULATE(

    [Measure0],
   'Accounting'[Fiscal Period] >= _FY)



This dax might or might not work depending on your data, but the basic idea should function with some changes.







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi Again @ValtteriN ,

 

The formula looks working and in a fast way, but it show a mistake: it computes the calculation for the last value from 'Price List' "2023004" while 'accounting' fact table has arrived only at '2023003'. 

 

I have tried many changes like inverting this filter 'accounting period'[Fiscal Period] =MAX( 'Price Lists'[Fiscal Period]) in 'Price List'[Fiscal Period] =MAX( 'accounting'[Fiscal Period]) but no result. 

Instead I found this version working (but not showing result in total)

 

Test =

VAR FY_ = CALCULATE( MAX( 'Price List'[Fiscal Period]), FILTER( 'Accounting', 'Accounting'[Fiscal Period] <= MAX('Price List'[Fiscal Period] ) ) )
return

CALCULATE(

    [Measure0],
   'Accounting'[Fiscal Period] >= FY_ )
 
What do you think? Could you help me in fix the total not showing ?
 
E

Hi,

Now that the issue is total not showing I would recommend checking this article by SQLBI:
Obtaining accurate totals in DAX - SQLBI

In short try utilizing methods such as SUMX to get the total value. 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors