Dynamic Row-Level Calculation in Matrix (Different Logic per Row) - Scalable Approach?

[manvishah17]

Hello all,

We need to build a matrix/table in Power BI where each row/column represents a different specification/metric, and each requires a completely different calculation logic.

For example:

• Spec A → Weighted average logic
• Spec B → SLA-based calculation
• Spec C → Ratio (e.g., Cost/Revenue)
• Spec D → Custom score logic

Additionally, in some cases, the calculation may also vary based on the column header context.

Key Requirements:

• Each row should use a different calculation logic
• Logic is fully different across specs (not reusable patterns)
• Calculations may come from different tables
• In some scenarios, logic may also vary by column context
• Should work within a single matrix/table visual
• Scalable and easy to maintain
• Optimised for performance (avoid resource exceeded errors)

Model Context:

• Approx number of specifications: 50+
• Logic sharing: No common base logic (fully different calculations)
• Data sources: Multiple tables involved per calculation

Challenges Faced:

• Using large SWITCH(TRUE()) or nested IF conditions becomes extremely heavy and hard to maintain
• Performance issues and “resource exceeded” errors as logic scales
• Difficult to manage when logic spans multiple tables and contexts
• Handling dynamic behaviour across both rows and columns adds complexity

Looking for Suggestions:

• What is the best scalable pattern to handle such dynamic row-level logic?
• Is a disconnected table + measure mapping still viable at this scale (50+ specs)?
• How to efficiently handle cases where logic also depends on column context?
• Any best practices to avoid performance bottlenecks in such designs?

[Hans-Georg_Puls]

Hi @manvishah17 ,

another option could be a matrix with calculation groups as columns and rows. For every cell the combination of two calculation items is set:

1. Define a Dummy measure like (Dummy = 0) and assign it to the value section of the matrix. The definition of this measure is irrelevant, it is just a Dummy ensuring that the calculation group items are called
2. Define a Calculation Group DynamicRows with one Calculation Item for every Row
3. Define a Calculation Group Dynamic Columns with one Calculation Item for every Column
4. Decide if the most of your calculations are the same for a row or a column. Depending on this give the column or the row calculation group the higher precedence value. The items of the calculation group with the lower precedence will be calculated first, then the items of the calculation group with the higher precedence. If you want to change this behaviour for single cells, read the following steps carefully.
5. Define all the calculation items of the two calculation groups
   1. Unlike the usual procedure don't use SELECTEDMEASURE in any of these calculation items
   2. Items of the calculation group with the lower precende can be defined as = 0, because they will never relevant, unless you want to change the priority
   3. Define any calculation that you want
   4. If you want to change the priority for single cells, do the following
      (Assuming the dynamic rows calculation group has the higher precedence and that you want the result of the column with ordinal = 1 for row "Row 2" )
      
      Row 2 =

      VAR _selectedcol = SELECTEDVALUE('Dynamic Columns'[Ordinal])

      RETURN

          IF(_selectedcol = 1, SELECTEDMEASURE(), "Row 2")
      In this case SELECTEDMEASURE () is allowed. It gives you the result of the calculation item from calculation group Dynamic Columns. Based on this example you can realize more complex scenarios depending on row and column and of course your needs
6. Assign Dynamic Rows to the rows of the matrix visual, disable rows if you don't want them at the "filters on this visual" section
7. Assign Dynamic Columns to the columns of the matrix visual, disable columns if you don't want them at the "filters on this visual" section

Your calculation group definitions should look similar to this in model view:

Check in table view if ordinal numbers are assigned to the calculation items. If not change them by drag and drop:

Assuming that rows have precendence a typical row calculation group item would look like this:
Row 1 = "Row 1"

A typical column item definition would look like this:
Col 1 = 0
If you want to change the priority for column 2 and row 2, you need something like:

Col 2 = "Col 2"

and

Row 2 =

VAR _selectedcol = SELECTEDVALUE('Dynamic Columns'[Ordinal])

RETURN

    IF(_selectedcol = 1, SELECTEDMEASURE(), "Row 2")

The matrix of my little demo:

Hope that helps.

[SharmilaBrisca]

Hi ,

Try to Put your spec names in a disconnected table → use it as Matrix rows → one master measure checks which spec is selected and runs the right calculation.

1. Disconnected Spec Table:

Spec Table =
DATATABLE( "SpecID", INTEGER, "SpecName", STRING, { { 1, "Spec A" }, { 2, "Spec B" }, { 3, "Spec C" } } )

No relationship needed. Use SpecName on Matrix Rows.

2.Split into Bucket Measures (NOT one giant SWITCH)

_Bucket1 =
                        SWITCH(
                                          SELECTEDVALUE( 'Spec Table'[SpecID] ),
                                          1, [Weighted Avg Logic],
                                          2, [SLA Logic], BLANK() )

_Bucket2 =
                        SWITCH(
                                          SELECTEDVALUE( 'Spec Table'[SpecID] ),
                                          3, [Cost/Revenue Logic],
                                          4, [Custom Score Logic], BLANK() )

3.Master Measure (routes to the right bucket)

Master KPI =
                          VAR _id = SELECTEDVALUE( 'Spec Table'[SpecID] )
 RETURN
 SWITCH(
                    TRUE(),
                     _id <= 15, [_Bucket1],
                     _id <= 30, [_Bucket2],
                     _id <= 50, [_Bucket3] )

Only one bucket fires per row — this is what prevents performance issues.

Always follow the below steps:

• Group specs into buckets of ~15 — never one giant SWITCH with 50 branches
• Always use var .. = selectedvalue(.....) once at the top
• Turn off row subtotals — they force all branches to fire at once
•  Never write all 50 specs in a single SWITCH — causes resource exceeded errors

Thanks and Regards,

GainInsights Solutions
Trusted Microsoft Partner - https://gain-insights.com/partnerships/microsoft/
www.gain-insights.com