Forum Discussion

emarome94's avatar
emarome94
Icon for Helper I rankHelper I
3 years ago
Solved

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= CALCU...
CALCULATE
slow rendering
Slow Reports
  • ValtteriN's avatar
    ValtteriN
    3 years ago

    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. 

emarome94's avatar
emarome94
Icon for Helper I rankHelper I
3 years ago

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]) )

 

 

ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity Champion
3 years ago

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.



  • emarome94's avatar
    emarome94
    Icon for Helper I rankHelper I
    3 years ago

    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
    • ValtteriN's avatar
      ValtteriN
      Icon for Community Champion rankCommunity Champion
      3 years ago

      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.