Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
JRogersLebara
Frequent Visitor

DAX Performance Tuning - Income Statement

I have created an Income Statement using 'Financial Reporting Matrix' by ProfitBase (screenshot below with values removed for sensitivity).

The performance of the visual is very slow despite only having 100k rows in the General Journal data - changing a filter takes 11-20 seconds and even longer when published to Power BI Service.

JRogersLebara_0-1670859526286.png

 

The basic data model is as follows (for demonstration purposes I've removed other lookup tables such as Calendar, Entity etc):

JRogersLebara_1-1670859674630.png

 

- 'P&L Rank' is a manually written template for the income statement:

 

JRogersLebara_2-1670859741068.png

- 'Main Accounts' is a mapping table that assigns each GL account a category (same categories as the 'Metric' column in the 'P&L Rank' table)

 

- 'General Journal' is the fact table

 

The 'row' section of the matrix contains the following Measure:

P&L ACTUALS = 
SWITCH(
    MAX('📋P&L RANK'[MEASURE]),
    0, [ACTUAL] / SELECTEDVALUE('🔎UNITS'[DIVISION]),
    1, '_GLOBAL MEASURES'[GROSS REVENUE ACTUALS] / SELECTEDVALUE('🔎UNITS'[DIVISION]),
    2, [MOBILE PROFIT ACTUALS] / SELECTEDVALUE('🔎UNITS'[DIVISION]),
    3, [MOBILE PROFIT % ACTUALS],
    4, [GROSS PROFIT ACTUALS] / SELECTEDVALUE('🔎UNITS'[DIVISION]),
    5, [GROSS MARGIN ACTUALS],
    6, [TOTAL OPEX ACTUALS] / SELECTEDVALUE('🔎UNITS'[DIVISION]),
    7, [EBITDA PRE-EXCEPTIONALS ACTUALS] / SELECTEDVALUE('🔎UNITS'[DIVISION]),
    8, [REPORTED EBITDA ACTUALS] / SELECTEDVALUE('🔎UNITS'[DIVISION]),
    9, [WORKING CAPITAL MOVEMENTS ACTUALS] / SELECTEDVALUE('🔎UNITS'[DIVISION]),
    10, [OPERATING CASHFLOW ACTUALS] / SELECTEDVALUE('🔎UNITS'[DIVISION]),
    11, [CASH CONVERSION RATIO ACTUALS]
)

 

An example of the measures referenced in the above 'P&L Actuals' measure is as follows:

GROSS PROFIT ACTUALS = 
SUMX(
    FILTER(
        ALL('📋P&L RANK')
        ,'📋P&L RANK'[INDEX] IN {1, 2, 3, 4, 8, 9, 10}
        )
    ,'📒GENERAL JOURNAL'[ACTUAL]
)

 

Performance Analyser:

JRogersLebara_3-1670860091917.png

 

My suspicion is that the FILTER argument is what's causing the performance issues but I haven't been able to work out an alternative solution - any ideas Power BI community?

 

3 REPLIES 3
amitchandak
Super User
Super User

@JRogersLebara , Measure looping can slow down measure.

 

Can you move the measure calculation(from base column) in the measure P&L as var and then try

Hi @amitchandak , are you able to clarify what you meant in your reply, please?

 

Thanks

Hello @amitchandak, I am not sure what you mean, could you please clarify?

Thanks 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors