Forum Discussion
Helper IVPerformance Concerns with Manual Row-Level Table and SWITCH Function
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-
Memorable MemberHi 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:
| 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:
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