DAX Performance Tuning - Income Statement

[JRogersLebara]

NOTE: I posted this question before (2962974) but did not receive an adequate solution so re-posting here

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.

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

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

- '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:

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?

[selimovd]

Hey  @JRogersLebara ,

it's always difficult to help in cases like that when there is no file available, but I understand you cannot share because of sensitive information.

By just seeing the data you gave, I see two issues that could cause the low performance:

1. In your SWITCH you are searchching for the MAX('P&L RANK'[MEASURE]). But as I understand you don't go for the column [MEASURE] in your table, you look for the [METRIC]. It seem like a tiny transition, but it can have a huge impact on the result. To be honest I'm not quite sure how we could improve that if that's the issue. Our friends from SQLBI wrote an article about that and I once had a report where changing the condition from asking for an ID to the column changed from 40 seconds to 1.8 seconds:

Understanding the optimization of SWITCH - SQLBI

2. I also think the FILTER could cause the issue. You filter on the whole table and not on a column. That could make also a big impact. Maybe try to filter on the column. At least in my mind the result should be identical. Try the following measure:

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

Let me know if anything of that worked.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

[JRogersLebara]

Hi @selimovd , thanks a lot for the quick response.

The first solution is interesting and I would never have guessed there would be a performance difference - I'm just not sure how I would implement it in my case.

The second point referencing the [INDEX] column is one I had already tried but for some reason when I make this slight change I get zero values returned from the query. Very confused as to why that is happening as it seems perfectly logical!

I have spent time creating a pared-down version of my file with randomised data and all other sensitive data removed here:

https://www.dropbox.com/s/v8oc7bk7umj1jmq/Power%20BI%20Example.pbix?dl=0

Kind regards,

Jake

[ppm1]

Thank you for sharing a pbix, which really helps you get better proposed solutions. With all the nested and complex measures, it is hard to troubleshoot. However, here are a few things that should make a difference.

1. Turn off Auto Date/Time in your file (won't make much perf difference but a very good practice to reduce file size and memory footprint).

2. (Probably your 2nd biggest issue) As suggested in other solution, don't filter entire tables in your CALCULATE. Ideally just one column at a time.

3. (Probably the biggest issue) - many of your base measures use this in the expression, which is a very bad practice.

SUMX(Data, [Actual])

You should just use [Actual] instead. This avoid iterating over the whole Data table triggering calculating that measure on every row. Replace that everywhere and things should improve.

4. Use variables whenever the same expression is used multiple times in an expression. You use SELECTEDVALUES(Units[Division]) a lot, and could get that value once up front and use it throughout.

5. You should also look into Field Parameters and/or Calculation Groups to get your SWITCH functionality in a different/better way.

Pat

[selimovd · Accepted solution]

Hello @JRogersLebara ,

I tried to understand what exactly is causing the longer execution time.

In general I didn't find a single point that is causing the performance. From my point of view it seems to be more a nesting of many measures that are all calculated in the report.

For example the main table is calling 11 different measure types in the SWITCH. Each measure calls another 2-3 measures and each of these 2-3 measures is calculating up to 10 and probably more measures. All of these measures have to be calculated during the execution and that takes a few seconds.

For example when we focus of the measure [ P&L ACTUALS YTD LAST YEAR VARIANCE %] and we focus only on the MEASURE 11 "Cash Conversion ratio" of the 'P&L Rank' table, then this measure is calculating:

[CASH CONVERSION RATIO ACTUALS YTD] - [CASH CONVERSION RATIO ACTUALS YTD LAST YEAR]

The first measure [CASH CONVERSION RATIO ACTUALS YTD] is then calculated by:

[OPERATING CASHFLOW ACTUALS YTD] / [EBITDA PRE-EXCEPTIONALS ACTUALS YTD]

And [OPERATING CASHFLOW ACTUALS YTD] is calculating:

[WORKING CAPITAL MOVEMENTS ACTUALS YTD] + [EBITDA PRE-EXCEPTIONALS ACTUALS YTD]

And [WORKING CAPITAL MOVEMENTS ACTUALS YTD] is calculating:

[INVENTORIES ACTUALS YTD]
+ [MNO BALANCES ACTUALS YTD]
+ [CARRIER BALANCES ACTUALS YTD]
+ [STAFF BALANCES ACTUALS YTD]
+ [3RD PARTY BALANCES ACTUALS YTD]
+ [VAT BALANCES ACTUALS YTD]
+ [NON-CURRENT RECEIVABLES ACTUALS YTD]
+ [PROVISIONS ACTUALS YTD]
+ [DERIVATIVES ACTUALS YTD]
+ [DEFERRED INCOME ACTUALS YTD]

I don't even want to digg deeper into the calculation of the last 10 measures and now I just focused on the first measure of each calculation. But you have to be aware that the nesting of so many functions has to be calculated these are around 40 calculations only for MEASURE 11. You are calculating another 10 MEASUREs in your table. For the amount of calculation I think the speed is actually OK.

In my point of view the problem is the way you nest the calculations.

For the normal table from your example more than 1500 queries are executed (and still need only around 1.8 seconds). That is waaaaayyy to many queries. But the way you created a snowflake schema and nested the measures, it's difficult to rebuild them.

One improvement I still found. You filter many times on a whole table. Don't filter on tables, always filter on columns. For example instead of:

FILTER(
    ALL('📋P&L RANK')
    ,'📋P&L RANK'[INDEX] IN {1, 2, 3, 4, 8, 9, 10, 14, 15, 16, 17, 18}
)

filter on the column:

FILTER(
    ALL('📋P&L RANK'[INDEX])
    ,'📋P&L RANK'[INDEX] IN {1, 2, 3, 4, 8, 9, 10, 14, 15, 16, 17, 18}
)

This can make a huge difference in performance. In your case it's a small improvement, but I would try to make it a habit to filter on columns and not tables.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic