Forum Discussion

a4's avatar
a4
Icon for Helper III rankHelper III
6 years ago

How to reduce the Dax query

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

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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.

     

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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. 

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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