Dynamic Number of Columns With N Columns - Table Visual

[sinfa]

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		def@gmail.com
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		abc@gmail.com	Person B	456	def@gmail.com
2	Shop B	UK	London	Westminster	123	abc@gmail.com	Person B		def@gmail.com
3	Shop C	UK	London	Westminster	Person A	123	abc@gmail.com	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	abc@gmail.com
1	Contact 2 Name	Text	Person B
1	Contact 2 Phone	Whole Number	456
1	Contact 2 Email	Text	def@gmail.com
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	abc@gmail.com
2	Contact 2 Name	Text	Person B
2	Contact 2 Phone	Whole Number
2	Contact 2 Email	Text	def@gmail.com
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	abc@gmail.com
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!

[v-yangliu-msft]

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

[sinfa]

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		abc@gmail.com	Person B	456	def@gmail.com
2	Shop B	UK	London	Westminster	123	abc@gmail.com	Person B		def@gmail.com
3	Shop C	UK	London	Westminster	Person A	123	abc@gmail.com	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!

[ALLUREAN]

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

[sinfa]

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!