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-
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.
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 CommunityHi 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).
-