No enough memory to complete this operation when loadin measure into matrix

[gorgo]

Hi Everyone,

im working on power bi report where i need to multiply "Losses 3 Years Rate" measure values based on Index Year in matrix

below is the formula of "Losses 3 Years Rate":

Losses 3 Years Rate =


IFERROR(DIVIDE(
CALCULATE (     
   [Incurred Claims],      
   FILTER (      
      ALL( LineReport[Index Year],LineReport[UW Year]),      
      LineReport[Index Year] <= MAX ( LineReport[Index Year]) &&
      LineReport[UW Year] >= MAX(LineReport[UW Year])-2 && LineReport[UW Year] <= MAX(LineReport[UW Year])
        
   )   
)
,
if( SELECTEDVALUE(LineReport[Index Year])=1,
CALCULATE(
   [Incurred Claims],      
   FILTER (      
      ALL( LineReport[Index Year],LineReport[UW Year]),      
      LineReport[Index Year] <= MAX ( LineReport[Index Year]) &&
      LineReport[UW Year] >= MAX(LineReport[UW Year])-2 && LineReport[UW Year] <= MAX(LineReport[UW Year])
   )
),
CALCULATE (     
   [Incurred Claims],      
   FILTER (      
      ALL( LineReport[Index Year],LineReport[UW Year]),      
      LineReport[Index Year] <= MAX ( LineReport[Index Year])-1 &&
      LineReport[UW Year] >= MAX(LineReport[UW Year])-2 && LineReport[UW Year] <= MAX(LineReport[UW Year])
    )
)


),0)

it is properly generated as per the below screen shot:

now i need to calculate Developement Factor which is the "Losses 3 years Rate" for index year multiply by the others for example:

Dev Fac for index 1 = Rate1 * rate2*rate3*rate4*rate5*rate6*rate7*rate8

Dev Fac for index 2 = rate2*rate3*rate4*rate5*rate6*rate7*rate8

Dev Fac for index 3 = rate3*rate4*rate5*rate6*rate7*rate8

Dev Fac for index 4 = rate4*rate5*rate6*rate7*rate8

Dev Fac for index 5 = rate5*rate6*rate7*rate8

Dev Fac for index 6 = rate6*rate7*rate8

Dev Fac for index 7 = rate7*rate8

Dev Fac for index 8 = rate8

Im using the below formula:

Cape Cod IL 3 Years Rate = 
var indexyear = SELECTEDVALUE(LineReport[Index Year])
return
EXP(
     SUMX(
          FILTER(ALL(LineReport), LineReport[Index Year]<=MAX(LineReport[Index Year]) && 
               LineReport[Index Year]>= indexyear)         
             ,           
          LN([Losses 3 Years Rate])
         )    
    )	

however the matrix is keep loading and throw a memrory error:

below is the formula i used in Matrix Value:

Rate Calculation = 
    VAR Val =
        SWITCH(SELECTEDVALUE('Chain Ladder'[Group]),
            "Premium 3 Years",[Premium 3 Years Rate],
            "Premium 5 Years",[Premium 5 Years Rate],
            "Premium 7 Years",[Premium 7 Years Rate],
            "Premium All Years",[Premium All Years Rate],
            "Premium Average",[Premium Average Years Rate],

            "Losses 3 Years", [Losses 3 Years Rate],
            "Losses 5 Years",[Losses 5 Years Rate],
            "Losses 7 Years",[Losses 7 Years Rate],
            "Losses All Years",[Losses All Years Rate],
            "Losses Average",[Losses Average Years Rate],

            "Development Factor",'Cape Cod'[Cape Cod IL 3 Years Rate] 
    )
    RETURN 

         FORMAT(Val, "0.00000")

appreciate your assistance since i cant go any further with this.

Best regards,

Georges

[AlB]

Hi @gorgo 

What about the code for [Incurred Claims]? Would it be possible for you to share the pbix?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[gorgo]

HI, incurred claims is a simple sum formula :

Incurred Claims = SUM(LineReport[Paid Claims])+[Outstanding Claims]

Outstanding Claims = if (SELECTEDVALUE('Year Type'[Type])="FY",
                    CALCULATE(SUM(LineReport[OS Claims]),LineReport[Last OS Position FY]=1), 
                    CALCULATE(SUM(LineReport[OS Claims]),LineReport[Last OS Position UW Year]=1))

Last OS Position FY = IF(AND([Max Period From FY]=  LineReport[Premium Period From],[Max Transaction From FY]=LineReport[Transaction Date]),1,0)

Last OS Position UW Year = IF(LineReport[Statement Type]="Claim Statement" ,
                            IF(LineReport[Transaction Date]=[Max Transaction Date],1,0),
                            IF(AND([Max Period From]=LineReport[Premium Period From],[Max Transaction Date]=
                            LineReport  [Transaction Date]),1,0))

these are formulas involved.

note that i used Incurred Claims in other formulas and other tables, and it was generated normally

thanks

[Greg_Deckler]

@gorgo - First, appreciate all of the information but this will be difficult to troubleshoot without recreating it and for that would need sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

With that said, you may be able to improve the performance of your DAX measures. For example, IFERROR is an expensive DAX function and you don't need it because you are already using DIVIDE which has built in error checking as an optional 3rd parameter. So I would get rid of that. 

Other thoughts, you do not include your formula for Incurred Claims so there may be optimizations there. Also, do you really need ALL or can you use something like ALLEXCEPT or some other base filter, or maybe REMOVFILTERS perhaps? It is expensive to start with ALL as this is going to eat up a lot of memory when doing the DAX calculation.