Forum Discussion
Dynamic Row-Level Calculation in Matrix (Different Logic per Row) - Scalable Approach?
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?
Hi manvishah17 ,
For calculation groups, it’s key to note that they do not require a shared or base measure. The measure you add to the Values well acts as a trigger, and when the visual is evaluated, the engine substitutes each calculation item’s logic in place of the measure. This allows each row to have independent logic, even across different tables. SELECTEDMEASURE becomes relevant only when you need calculation groups to interact, such as passing logic between rows and columns. If that’s not required, each calculation item can remain self-contained, and you can disregard SELECTEDMEASURE.
Regarding the SWITCH and bucket method, your concern about execution is well-founded. While SWITCH appears to evaluate just one branch, the DAX engine doesn’t always short-circuit as traditional languages do. With many branches, especially across different tables, the engine may evaluate more logic than expected, leading to higher memory usage and possible resource errors.By splitting logic into buckets, you help the engine focus on a smaller group of calculations, reducing the dependency tree and storage engine queries. Storing the selected specification in a variable further optimizes performance by avoiding repeated filter context evaluations.
Both approaches are valid and serve different needs. Calculation groups offer flexibility for scenarios requiring interaction between rows and columns in a visual. The bucketed measure approach is generally easier to manage and performs well if each bucket is kept to a reasonable size. If your column logic is relatively static, the bucketed approach is often more straightforward.
Thank you.
7 Replies
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:
- 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
- Define a Calculation Group DynamicRows with one Calculation Item for every Row
- Define a Calculation Group Dynamic Columns with one Calculation Item for every Column
- 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.
- Define all the calculation items of the two calculation groups
- Unlike the usual procedure don't use SELECTEDMEASURE in any of these calculation items
- 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
- Define any calculation that you want
- 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])RETURNIF(_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
- 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
- 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])RETURNIF(_selectedcol = 1, SELECTEDMEASURE(), "Row 2")The matrix of my little demo:
Hope that helps.
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.comHi manvishah17 ,
I would take a moment to thank Hans-Georg_Puls for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.Hi manvishah17 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.Hey,
I looked at the solutions provided but I have a query regarding Calcualtion group like I don't have any similar based measure, so how it will work.
Moreover other Switch case approach, I am aligned with using Int but creating Buckets, still have query how the exceution engine works.Hi manvishah17 ,
For calculation groups, it’s key to note that they do not require a shared or base measure. The measure you add to the Values well acts as a trigger, and when the visual is evaluated, the engine substitutes each calculation item’s logic in place of the measure. This allows each row to have independent logic, even across different tables. SELECTEDMEASURE becomes relevant only when you need calculation groups to interact, such as passing logic between rows and columns. If that’s not required, each calculation item can remain self-contained, and you can disregard SELECTEDMEASURE.
Regarding the SWITCH and bucket method, your concern about execution is well-founded. While SWITCH appears to evaluate just one branch, the DAX engine doesn’t always short-circuit as traditional languages do. With many branches, especially across different tables, the engine may evaluate more logic than expected, leading to higher memory usage and possible resource errors.By splitting logic into buckets, you help the engine focus on a smaller group of calculations, reducing the dependency tree and storage engine queries. Storing the selected specification in a variable further optimizes performance by avoiding repeated filter context evaluations.
Both approaches are valid and serve different needs. Calculation groups offer flexibility for scenarios requiring interaction between rows and columns in a visual. The bucketed measure approach is generally easier to manage and performs well if each bucket is kept to a reasonable size. If your column logic is relatively static, the bucketed approach is often more straightforward.
Thank you.
