Forum Discussion
Dynamic Number of Columns With N Columns - Table Visual
Hello,
I am trying to create a drillthrough where the columns on the table visual are dynamically selected, and would really appreciate your help!
Basically, I have a summary table that shows a list of business rules and a count of locations that triggered it today only. When I click on a location count, I want to create a drillthrough page that'll show the list of locations and a bunch of location data that's related to the business rule. There are over 100 business rules and each location got several 100 columns too. The issue I'm facing is I only want to display columns that are relevant for each business rule, and the number of columns will vary depending on the business rule. There will be times where the relevant columns will overlap for different business rules. Here's what the summary table looks like:
| BusinessRuleID | LocationCount |
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
Is there a way where I can a table visual that'll dynamically pick the columns I need based on the business rule that I'm drilling through?
Here's a few examples of what I'm looking for:
If business rule #1 is about geographic consistency, on the drillthrough, I'll like the table visual to show LocationID, Location, Country, and City.
| LocationID | LocationName | Country | City |
| 1 | Shop A | UK | Munich |
If business rule #3 is about whether contact 2 details are all filled in, on the drillthrough, I'll like the table visual to show LocationID, Location, Contact 2 Name, Contact 2 Phone, Contact 2 Email.
| LocationID | LocationName | Contact 2 Name | Contact 2 Phone | Contact 2 Email |
| 2 | Shop B | Person B | [email protected] | |
| 3 | Shop C | Person B | 456 |
Here's the underlying data that I have at the moment:
RESULTS - A table containing all the locations, what business rules did it trigger, and what date (today's date is 20th Oct 2023 for me)
| ResultsID | LocationID | BusinessRuleID | Date |
| 1 | 1 | 1 | 18/10/23 |
| 2 | 1 | 1 | 19/10/23 |
| 3 | 1 | 1 | 20/10/23 |
| 4 | 1 | 2 | 20/10/23 |
| 5 | 2 | 2 | 20/10/23 |
| 6 | 2 | 3 | 20/10/23 |
| 7 | 3 | 3 | 20/10/23 |
BUSINESS RULES - A table with list of business rules, as well as thier name and description
| BusinessRuleID | Name | Description |
| 1 | Inconsistent Geography (High Level) | Country and City don't match |
| 2 | Incomplete Contact 1 Details | There are blanks on contact 1's name, email or phone - they're all compulsory |
| 3 | Incomplete Contact 2 Details | There are blanks on contact 2's name, email or phone - they're all compulsory |
For LOCATION data, here's the original table:
| LocationID | Location | Country | City | Borough | Contact 1 Name | Contact 1 Phone | Contact 1 Email | Contact 2 Name | Contact 2 Phone | Contact 2 Email |
| 1 | Shop A | UK | Munich | Westminster | Person A | [email protected] | Person B | 456 | [email protected] | |
| 2 | Shop B | UK | London | Westminster | 123 | [email protected] | Person B | [email protected] | ||
| 3 | Shop C | UK | London | Westminster | Person A | 123 | [email protected] | Person B | 456 |
Lastly, I believe I can transpose/pivot the data, and got stuck on what to do next:
| LocationID | ColumnName | ColumnType | ColumnValue |
| 1 | Location | Text | Shop A |
| 1 | Country | Text | UK |
| 1 | City | Text | Munich |
| 1 | Borough | Text | Westminster |
| 1 | Contact 1 Name | Text | Person A |
| 1 | Contact 1 Phone | Whole Number | |
| 1 | Contact 1 Email | Text | [email protected] |
| 1 | Contact 2 Name | Text | Person B |
| 1 | Contact 2 Phone | Whole Number | 456 |
| 1 | Contact 2 Email | Text | [email protected] |
| 2 | Location | Text | Shop B |
| 2 | Country | Text | UK |
| 2 | City | Text | London |
| 2 | Borough | Text | Westminster |
| 2 | Contact 1 Name | Text | |
| 2 | Contact 1 Phone | Whole Number | 123 |
| 2 | Contact 1 Email | Text | [email protected] |
| 2 | Contact 2 Name | Text | Person B |
| 2 | Contact 2 Phone | Whole Number | |
| 2 | Contact 2 Email | Text | [email protected] |
| 3 | Location | Text | Shop C |
| 3 | Country | Text | UK |
| 3 | City | Text | London |
| 3 | Borough | Text | Westminster |
| 3 | Contact 1 Name | Text | Person A |
| 3 | Contact 1 Phone | Whole Number | 123 |
| 3 | Contact 1 Email | Text | [email protected] |
| 3 | Contact 2 Name | Text | Person B |
| 3 | Contact 2 Phone | Whole Number | 456 |
| 3 | Contact 2 Email | Text |
Is what I'm trying to do even possible?
Thanks a bunch!
4 Replies
Hi,
You can use field parameters to dynamically pick up the columns.
https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters
Thanks for your reply 🙂
I thought about using field parameters as well, but not exactly sure how to implement it. Do you mind elaborating a bit more?Thanks!
Hi sinfa ,
Here are the steps you can follow：
1. Create measure.
Measure = var _select=SELECTEDVALUE('Table A'[BusinessRuleID]) var _column=SELECTCOLUMNS(FILTER(ALL('Table A'),'Table A'[BusinessRuleID] =_select),"LocationID",[LocationID]) return SWITCH( TRUE(), _select = 1 &&MAX('Table'[ColumnName]) in {"Country","Location","City"},MAX('Table'[ColumnValue]), _select =3 && MAX('Table'[ColumnName]) in {"Location","Contact 2 Name","Contact 2 Phone","Contact 2 Email"},MAX('Table'[ColumnValue]),BLANK())2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi Liu Yan,
Thanks for your reply.
I'm getting an issue where the visual has exceeded the available resources, which might be due to the volume of the data.
Would it be possible to do something similar if Table A was actually pivoted like the original LOCATION table, like this:
LocationID Location Country City Borough Contact 1 Name Contact 1 Phone Contact 1 Email Contact 2 Name Contact 2 Phone Contact 2 Email 1 Shop A UK Munich Westminster Person A [email protected] Person B 456 [email protected] 2 Shop B UK London Westminster 123 [email protected] Person B [email protected] 3 Shop C UK London Westminster Person A 123 [email protected] Person B 456 Also, would it be possible to automate the selection without hardcoding it in the switch by adding a new table inbetween Table and Table A that'll look like this:
BusinessRuleID ColumnName ColumnOrder 1 Country 1 1 City 2 3 Contact 2 Email 1 3 Contact 2 Name 2 3 Contact 2 Phone 3 Thanks for your help again, really appreciate it!
