Performance Concerns with Manual Row-Level Table and SWITCH Function

[bdpr_95]

Hi everyone,

I’m working on a Power BI report where I needed to create a table visual with very specific rows. These rows don’t come from a data source, so I had to manually define them using a calculated table (36 rows in total).

To handle logic based on these rows, the developer implemented a SWITCH function with 36 conditions, one for each row. Everything seems to be working fine for now, but I’m concerned that this approach might lead to performance issues down the line.

My question is:
- Is there a better or more efficient way to handle this kind of scenario?
- Are there alternative approaches to avoid such a large SWITCH statement while still maintaining flexibility and performance?

Any insights or best practices would be greatly appreciated!

[rohit1991]

Hi @bdpr_95 

Instead of writing a huge SWITCH, build a mapping table (either directly in Power BI or even in Excel). Just list your custom rows and logic or attributes as columns—think of it like a mini dimension table for your business rules.

Then, use LOOKUPVALUE or create a relationship to pull in those values right inside your measures. Example:

Result =
LOOKUPVALUE(
    'LogicTable'[CalculationType],
    'LogicTable'[RowKey], SELECTEDVALUE('ManualRows'[RowKey])
)

If you’re working with lots of different calculations or formatting, Calculation Groups in Tabular Editor are also a game changer. Try to keep business rules in your data model/tables as much as possible, not inside a huge SWITCH or IF. It’ll save you time and headaches later.

[bdpr_95]

Rohit, can you help me? I've never done this before. Let's assume a simpler use case, instead of 36 possibilities, we only have 3. So, I'm going to create a calculated table where the first column contains the names of the rows I want to display in my visual table, and then I'll add another column with an ID (1, 2, 3), just in case it's needed.

After that, what I was doing was creating 3 separate measures. Note that although some of them are based on the same field to filter the measure, others use different fields. Let's assume the following:

• Measure 1: CALCULATE(SUM(Table[Column1]), KEEPFILTERS(Table[Column2] = 1))
• Measure 2: CALCULATE(SUM(Table[Column1]), KEEPFILTERS(Table[Column2] = 2))
• Measure 3: CALCULATE(SUM(Table[Column1]), KEEPFILTERS(Table[Column3] = 45))
  
  How can I build something like you mentioned?

[v-aatheeque]

Hi @bdpr_95 

Thanks for your question!

Based on your described scenario  where you have 3 measures with different filter logic, and you're looking to display them dynamically in a visual using a calculated table, I have recreated a simplified example that might help you.

for your better understanding i am attaching PBIX file , let me know if you need futher assistance!!

Expected output :

Hope this helps !!

[bdpr_95]

Hi @v-aatheeque , what’s the difference between what you shared with me and what I currently have? You also have a SWITCH function with a measure for each possibility, so you’d end up with 36 possibilities (considering the entire universe), exactly like what I have currently.

[v-aatheeque]

Hi @bdpr_95 

That’s a great point, and I understand your concern about the SWITCH logic still being present. To better assist your current setup and explore if we can streamline it differently maybe even eliminate the SWITCH entirely, I would  really appreciate if you could share a small sample of your dataset.

Could you provide:

• A few rows from your base table showing Column1, Column2, and Column3

• A list or table of how you want the final visual to look (with expected values for Measure 1, 2, 3, etc.)

This will help me tailor the solution specifically to your logic, and potentially offer a more scalable approach for the 36+ possibilities you're managing.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

[bdpr_95]

Hi @v-aatheeque , I’ve already done that in message 5. I didn’t provide an example with data, but it’s clear that to get my final output, I need to have in my first column the names I want to display in my visual, and in the second column the metrics. These metrics, in certain cases, can be filtered using a column from the fact table (e.g., Column2 = 1), while in other cases it might be necessary to filter a different column (e.g., Column3 = 45).

[v-aatheeque]

Hi @bdpr_95 

To provide a solution that scales effectively (especially with 36+ conditions), we want to make sure without sample data or a clear mapping between your condition logic and the metric names.

For aunderstanding , could you provide:

• A few rows of example data from your base table (Column1, Column2, and Column3 ) just with dummy values.
• A clearer structure or example of the 36 conditions you're currently managing.
• An overview of how each measure interacts with your data model.

This will help us recommend a clean and scalable approach that avoids maintaining 36+ separate measures.

[v-aatheeque]

Hi @bdpr_95 
As earlier requested to provide further we need :

• A few sample rows from your base table (Column1, Column2, Column3) with dummy values.

• A clear example or outline of the 36 conditions you’re using.

• How each measure connects to your data model.

This will allow us to suggest a cleaner approach without needing to maintain so many separate measures.

looking forward for your response !!

[jaineshp]

Hi @bdpr_95,

Great question — creating a calculated table with custom rows and using a large SWITCH function can work, but it’s important to plan for scalability and performance. Here’s a breakdown of best practices and alternatives:

---

Challenges with Large SWITCH Statements

While SWITCH with 36 conditions may work now, it can:

• Increase DAX engine evaluation time, especially when combined with slicers or visuals

• Reduce readability and maintainability

• Create overhead during refresh and interaction

---

Recommended Alternatives

1. Use a Supporting Table Instead of SWITCH

Instead of hardcoding logic with SWITCH, define your rules in a separate table (either in Power BI or in Excel/CSV) like:

RowKey Category CalculationType

1	A	Type1
2	B	Type2

Then use LOOKUPVALUE or RELATED in your measures, or build relationships to apply logic more efficiently.

---

2. Use Mapping Tables + Measures

You can simplify logic by using conditional measures like:

Selected Logic =
CALCULATE(
MAX('LogicTable'[CalculationType]),
FILTER('LogicTable', 'LogicTable'[RowKey] = SELECTEDVALUE('ManualRows'[RowKey]))
)

Then use that in a SWITCH or even a TREATAS() if needed — this is more dynamic and scalable.

---

3. Leverage Calculation Groups (if using Tabular Editor)

If your logic is mostly formatting, KPIs, or variation across similar measures, Calculation Groups (via Tabular Editor) are powerful and eliminate the need for verbose SWITCH statements.

---

Best Practices

• Keep business logic in data/model, not just measures

• Avoid “hardcoding” where possible

• Document assumptions and row intent in the model for maintainability

Let me know if you’d like help converting your SWITCH logic into a reusable mapping table!

—
Best regards,
Jainesh Poojara / Power BI Developer

[FBergamaschi]

Please avoid SWITCH and use IF

If the number of conditions is low, they do not make any difference, but 36 conditions is a lot

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI