Forum Discussion

manvishah17's avatar
manvishah17
Solution Supplier
3 months ago
Solved

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:...
Need Help
Tips and Tricks
  • v-tejrama's avatar
    v-tejrama
    3 months ago

    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.

SharmilaBrisca's avatar
SharmilaBrisca
Resolver I
3 months ago

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