Optimizing Measure performance (open incidents running total)

[Thogen]

Dear All,

A couple of days ago I posted a topic for help with a measure which calculates the number of open incidents per day. I've already made some small adjustments since then.

• https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Open-Incidents-per-day/m-p/3968348

Objective of this measure is to calculate to number of incidents where the last status in the filter context of the visual is not closed.

The measure I created works in the sense that it shows the correct results. However, already in my test-environment which is only ~15.000 rows, the performance isn't great.

When I attempt to run the measure in my live environment with ~15.000.000 rows, it's completely un-useable, as in, I just get an error that it runs out of memory. 

As far as I can see there are 2 issues:

• The number of iterations on the facttable 
• A rolling date range is intensive as the datasize keeps getting larger day-by-day (_Date[Date] <= MaxDate)

I'm however at a loss on how to calculate this result in a different way. What would be the correct way to re-write this measure so that it's usable in a larger data-model?

For reference, my fact table (Raw_Transactions) looks something like the screenshots below.

Only connected table is a date table called '_date'.

Thanks in advance for the assistance!

Open Incidents (Summarize) = 

VAR MaxDate = MAX('_Date'[Date]) 

VAR Result =
CALCULATE(
    COUNTX(
        ADDCOLUMNS(
            SUMMARIZE(
                Raw_Transactions,
                Raw_Transactions[IncidentId],
                Raw_Transactions[CountOfTransaction]
            ),
            "@MaxTransaction", 
                VAR _Result =
                CALCULATE( 
                    MAX( Raw_Transactions[CountOfTransaction] ), 
                    ALLEXCEPT( Raw_Transactions, Raw_Transactions[IncidentId] ),
                    _Date[Date] <= MaxDate
                )

                RETURN
                IF(
                    _Result = Raw_Transactions[CountOfTransaction],
                    _Result
                )
        ),
        [@MaxTransaction]
    ),
    _Date[Date] <= MaxDate,
    Raw_Transactions[TransactionType] <> "Closed"    
)
    
RETURN 
Result

[Anonymous · Accepted solution]

Hi @Thogen ,

Thanks for the reply from lbendlin .

Your current requirement is that your measure can achieve the function, but the performance is too poor when there are 15,000 data. You are considering how to optimize the performance. Is my understanding correct?

I suggest you consider the following aspects:

1. When the data set is large, the complex Measure syntax will significantly affect the calculation performance. If possible, consider breaking down the measure into simpler intermediate measures.

2. Check the data model to see if it can be optimized. For example, delete unused columns, ensure that the column data type is correct, and possibly summarize the data at a higher level before importing the data into Power BI.

For more detailed guidance, please see:

Optimization guide for Power BI - Power BI | Microsoft Learn

Reasons why your Power BI report is slow & how to optimize | Performance Tuning | MiTutorials (youtu...

3. If the database you are using supports the view function, please process the data in the database first, so that Power BI only needs to query the summary table without performing complex calculations on millions of rows.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[lbendlin]

Look at the Query plan too, especially the Records column there.  That may indicate where you have cartesian products.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.