Forum Discussion
Performance Concerns with Manual Row-Level Table and SWITCH Function
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!
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-
10 Replies
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
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 CalculationType1 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 DeveloperHi 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.
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?
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 !!
