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...
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!