How to reduce the Dax query

[a4]

Hi ,

I have written down a dax query to calculate the annualised premium for leap year and non leap year.

The query is bit complex that's why it is throwing "resource limit exceeds error "in Power Bi Service.

Please find below the query:

Annualised Gross Perm = IF([Distinct Count]>1,
SUMX(

TOPN(1,

GROUPBY(
'Club Year Data','Club Year Data'[Related risk ref],'Club Year Data'[Related quoteref],'Club Year Data'[spl_stagingid],
'Club Year Data'[perdaygrossperm],'Club Year Data'[Trim name],'Club Year Data'[Broker Group Name],'Club Year Data'[Product Name],'Club Year Data'[startdate],'Year-Days'[Days in Year],'Club Year Data'[enddate],'Year-Days'[Year]),

'Club Year Data'[spl_startdate],DESC),

IF('Club Year Data'[startdate]<DATE(LOOKUPVALUE('Year-Days'[Year],'Year-Days'[Days in Year],366),2,29) &&
DATE(LOOKUPVALUE('Year-Days'[Year],'Year-Days'[Days in Year],366),2,29)<'Club Year Data'[enddate],
'Club Year Data'[perdaygrossperm]*366,'Club Year Data'[perdaygrossperm]*365)),

SUMX(

GROUPBY(
'Club Year Data','Club Year Data'[Related quoteref],'Club Year Data'[Related risk ref],'Club Year Data'[spl_stagingid],
'Club Year Data'[spl_perdaygrossperm],'Club Year Data'[Trim name],'Club Year Data'[Broker Group Name],'Club Year Data'[Product Name],'Year-Days'[Days in Year],'Club Year Data'[startdate],'Club Year Data'[enddate],'Year-Days'[Year]),

IF('Club Year Data'[startdate]<DATE(LOOKUPVALUE('Year-Days'[Year],'Year-Days'[Days in Year],366),2,29) &&

DATE(LOOKUPVALUE('Year-Days'[Year],'Year-Days'[Days in Year],366),2,29)<'Club Year Data'[enddate],
'Club Year Data'[perdaygrossperm]*366,'Club Year Data'[perdaygrossperm]*365))

)

Formula for distinct count=DISTINCTCOUNT(Club Year Data[Related Quote ref])

After writing this formula(Annualised Gross Perm) I have again grouped it :

Annual Gross Premium =

SUMX(GROUPBY('Club Year Data','Club Year Data'[Club Year],'Club Year Data'[Related risk ref],'Club Year Data'[Trim name],'Club Year Data'[Broker Group Name],'Club Year Data'[Product Name]),

[Annualised Gross Perm])

Please suggest any alternative formula to this.

Kind Regards

Amit Kumar

[v-deddai1-msft]

Hi @a4 ,

The error occurs when the measure attempts to query too much data for the server to complete the result with the available resources.

You need to delete remove unnecessary columns and use parameters in your measure:

Annualised Gross Perm =

VAR A =

    GROUPBY (

        'Club Year Data',

        'Club Year Data'[perdaygrossperm],

        'Club Year Data'[Broker Group Name],

        'Club Year Data'[Product Name],

        'Club Year Data'[startdate],

        'Year-Days'[Days in Year],

        'Club Year Data'[enddate],

        'Year-Days'[Year]

    )

RETURN

    IF (

        [Distinct Count] > 1,

        SUMX (

            TOPN ( 1, A, 'Club Year Data'[spl_startdate], DESC ),

            IF (

                'Club Year Data'[startdate]

                    < DATE ( LOOKUPVALUE ( 'Year-Days'[Year], 'Year-Days'[Days in Year], 366 ), 2, 29 )

                    && DATE ( LOOKUPVALUE ( 'Year-Days'[Year], 'Year-Days'[Days in Year], 366 ), 2, 29 ) < 'Club Year Data'[enddate],

                'Club Year Data'[perdaygrossperm] * 366,

                'Club Year Data'[perdaygrossperm] * 365

            )

        ),

        SUMX (

            A,

            IF (

                'Club Year Data'[startdate]

                    < DATE ( LOOKUPVALUE ( 'Year-Days'[Year], 'Year-Days'[Days in Year], 366 ), 2, 29 )

                    && DATE ( LOOKUPVALUE ( 'Year-Days'[Year], 'Year-Days'[Days in Year], 366 ), 2, 29 ) < 'Club Year Data'[enddate],

                'Club Year Data'[perdaygrossperm] * 366,

                'Club Year Data'[perdaygrossperm] * 365

            )

        )

    )

If that doesn't help, would you please inform us more detailed information( your  data(by OneDrive for Business)) if possible? Then we will help you more correctly. You may purchase Power BI Premium capacity for your organization, see What is Power BI Premium? , How to purchase Power BI Premium.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.

Best Regards,

Dedmon Dai

[AllisonKennedy]

@a4  Are you simply multiplying a daily rate times number of days in the year? I feel like this could be done with a much simpler DAX if you have a proper DimDate table, rather than a summarized Year-Days table. 

https://allisonkennedycv.blogspot.com/2020/04/dimdate-what-why-and-how.html

In your DimDate table you'll want to add the [Number of Days in Year] column. You could do this in the Query Editor by adding Start of Year, End of Year, and then doing Subtract Days, all from the Date button in the Add Column tab in the ribbon. 🙂 

If that doesn't help, please provide more info. 

[amitchandak]

@a4 , this formula needs to be revisited.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. Can you please explain the formula functionally with an example.

Appreciate your Kudos.