Slow measure performance for P&L table

DAX DAX Calculations
8

Hi Abdul

I’ve been here in my role as Finance Director. The trick is to keep the SWITCH measure as short as possible, by;

  1. multiple mapping of ledger codes to totals as well as their own lines
  2. creating line references for numerators and denominators in the lines which require division (or any other KPI) then look the numbers up by expanding the context on a report structure table.
    This way DAX can stay simple and for most of the time just add numbers up.

I answered a similar post a few weeks ago and gave a sample PBIX and excel structure of how I tackled this one to arrive at a star schema model. (see link below). FYI my measure kicked off a couple of years ago taking around 13 seconds to calculate. Now it’s less than 2 seconds. (Fact table 3 to 4 million rows; report table about 2500 rows, but then sliced for the relevant dept and business to around 50 rows displayed). All thanks to star schema and short SWITCH statement.

SWITCH function on Financial Reporting causing Resources Exceeded on PowerBI Web Service - DAX Calculations - Enterprise DNA Forum

Hope this helps

Pete

1 Like