Forum Discussion

bradlyjames's avatar
bradlyjames
New Member
1 year ago
Solved

Measure slows down report

I have a report with 2 data sources.

DS 1: Layout table that is 'Imported' and came from an excel file.  This merely helps me define the layout and order of my rows:

This is a small sample of my excel file.  For context, this works exactly as I am wanting.

 

DS 2: 'Direct query' of a fact view from the Data Lake.  I have created a custom column on this table that uses a DAX 'SWITCH' to create a corresponding 'BalanceSheetLayoutSectionID' column in this fact data source that then relates to the Layout table.

 

Issue:  In order to create the custom calculations needed for my report, I have the following measure:

BalanceSheet = 
VAR _SelectedHeader = SELECTEDVALUE(Layout_BalanceSheet[BalanceSheetLayoutSectionID])

VAR TotalCurrentAssets = CALCULATE([Amount], REMOVEFILTERS(Layout_BalanceSheet), 
                            Layout_BalanceSheet[BalanceSheetSectionLvl2] = "Current Assets")

VAR TotalPropertyAndEquipment = CALCULATE([Amount], REMOVEFILTERS(Layout_BalanceSheet),
                                Layout_BalanceSheet[BalanceSheetSectionLvl2] = "Property and Equipment")

VAR LessAccumulatedDepreciation = CALCULATE([Amount], 'reporting fGeneralLedgerEntries'[MAINACCOUNT] >= 1800000 &&
                                   'reporting fGeneralLedgerEntries'[MAINACCOUNT] <= 1852000)

VAR NetBookValue = TotalPropertyAndEquipment - LessAccumulatedDepreciation

VAR TotalNonCurrentAssets = CALCULATE([Amount], REMOVEFILTERS(Layout_BalanceSheet),
                            Layout_BalanceSheet[BalanceSheetSectionLvl2] = "Other Assets")

VAR TotalAssets = TotalCurrentAssets + NetBookValue + TotalNonCurrentAssets

VAR TotalCurrentLiabilities = CALCULATE([Amount], REMOVEFILTERS(Layout_BalanceSheet),
                                Layout_BalanceSheet[BalanceSheetSectionLvl2] = "Current Liabilities")

VAR TotalNonCurrentLiabilities = CALCULATE([Amount], REMOVEFILTERS(Layout_BalanceSheet),
                                Layout_BalanceSheet[BalanceSheetSectionLvl2] = "Non-Current Liabilities")

VAR LongTermDebtNet = CALCULATE([Amount], REMOVEFILTERS(Layout_BalanceSheet),
                        'reporting fGeneralLedgerEntries'[MAINACCOUNT] = 2750010 ||
                        'reporting fGeneralLedgerEntries'[MAINACCOUNT] = 2700500)

VAR LongTermDebtNetOfUnamoritized = TotalNonCurrentLiabilities - LongTermDebtNet     

VAR TotalEquity = CALCULATE([Amount], REMOVEFILTERS(Layout_BalanceSheet),
                    Layout_BalanceSheet[BalanceSheetSectionLvl2] = "SHAREHOLDERS EQUITY")

VAR Result = SWITCH(TRUE(),
    _SelectedHeader = "TotalCurrentAssets", CALCULATE([Amount], REMOVEFILTERS(Layout_BalanceSheet), 
                            Layout_BalanceSheet[BalanceSheetSectionLvl2] = "Current Assets"),
    _SelectedHeader = "TotalPropertyAndEquipment", TotalPropertyAndEquipment,
    _SelectedHeader = "NetBookValue", NetBookValue,
    _SelectedHeader = "TotalNonCurrentAssets", TotalNonCurrentAssets,
    _SelectedHeader = "TotalAssets", TotalAssets,
    _SelectedHeader = "TotalCurrentLiabilities", TotalCurrentLiabilities,
    _SelectedHeader = "TotalNonCurrentLiabilities", TotalNonCurrentLiabilities,
    _SelectedHeader = "LongTermDebtNetOfUnamoritized", LongTermDebtNetOfUnamoritized,
    _SelectedHeader = "TotalLiabilities", TotalCurrentLiabilities + LongTermDebtNetOfUnamoritized,
    _SelectedHeader = "TotalEquity", TotalEquity,
    _SelectedHeader = "TotalLiabilitiesAndShareHoldersEquity", TotalCurrentLiabilities + LongTermDebtNetOfUnamoritized + TotalEquity,
    [Amount])
    
Return Result

The '_SelectedHeader' allows me to know which line I am on in the report and to appropriately calculate it's value or simply give it the 'Amount', which means it is not a custom calculation.

This works exactly as expected, however, it takes the report matrix 20-25 seconds to get this all loaded.  

I feel like maybe my measure isn't done right or perhaps my data source design is incorrect.  Is there a way to speed this up?

  • Hi,
    You can ran each variable calculation individually to see if one in particular is causing the slowness.  Also you can use performance Analyzer in the Optimize tab, so that it can identify if there is something else that is causing the slowness base on the duration it takes to complete.  It could be that is a combination of the fields you are bringing in with the calculation is trying to perform. 

    In my own experiece I try to avoid creating very lengthy dax code, as it does cause slowness  particularly when you are working with thousands of records.

4 Replies

  • Hi,
    You can ran each variable calculation individually to see if one in particular is causing the slowness.  Also you can use performance Analyzer in the Optimize tab, so that it can identify if there is something else that is causing the slowness base on the duration it takes to complete.  It could be that is a combination of the fields you are bringing in with the calculation is trying to perform. 

    In my own experiece I try to avoid creating very lengthy dax code, as it does cause slowness  particularly when you are working with thousands of records.

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support

    Hi bradlyjames,

    Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to Bmejia for sharing valuable insights.

     

    Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

     

    Thank you for being part of the Microsoft Fabric Community.

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support

    Hello bradlyjames,

    We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

    Thank you.

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support

    Hello bradlyjames,

    Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

    Thank you.