Forum Discussion
Performance 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-
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.
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 !!
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.
