Dynamic column picker using a new table

[diewalchin]

Hi, I am stumbling across this problem for quite some while now but cannot resolve it. I draw a quick sketch of my thought and the given data/information.

So basically, I have a large table. Each column represents a process parameter for production.

The idea is to have a second table that groups the columns for the process parameters, e.g., as shown in the workflow figure (1a and 1b). (As hierarchical grouping of columns is as far as I know not supported) So basically when someone selects Group2 in a slicer (dropdown list), then the aim is to visualize column B and column D in a visual.

Thus, my idea is to create a new and dynamic table that checks the condition, which group is selected and outputs a new table with prepared data for visualization. For instance, I outlined how I would assume that my visuals looks like when I want to visualize the selection of Group2, which makes use of column B and D and shows both series as a line chart. For the line chart, I need x-axis information (time column from original table), one column for the series (these are the respective column names = process parameter) and one for the respective values (values for each column name and timestamp).

What I found so far while searching for a solution on the web, is creating a new table with a SELECTCOLUMNS() and UNION() function. However, I am failing to properly show my needed output. Especially, when taking the original timestamp into account. It is also quite important that no aggregation takes place like summarize, average or max.

I would be very thankful if someone has an idea on how to resolve this issue or can share his/her experience in modeling these data properly.

Thanks in advance for your support!

Edit: What I also tried so far is that I have created a new table (SelectionTable) by using "Enter Data" and hard-coded the groups (SelectionTable[Group]). These groups are later used for the slicer. Within the original/large table (orig_table), I have created a measure named "DisplayValue" with the following formula:

DisplayValue = IF(HASONEVALUE(SelectionTable[Group]), SWITCH(VALUES(SelectionTable[Group]), "Group1", SELECTEDVALUE(orig_table[Column1]), "Group2", SELECTEDVALUE(orig_table[Column2]), "Group3", SELECTEDVALUE(orig_table[Column3]), ), "Please select a value to display" )

Thus, I was able to select a group and show the DisplayValue respectively. However, here my problem is that I cannot show 2 columns in one visualization. Do I maybe need a UNION() here? If yes, it did not work out for me using e.g., "Group2", UNION(SELECTEDVALUE(), SELECTEDVALUE()). 

I appreciate any feedback or hints on how to resolve this issue. Thank you in advance!

[amitchandak]

@diewalchin , Based on what I got. You need to unpivot the first table and need to have table with column and group to filter joined with this table 

https://radacad.com/pivot-and-unpivot-with-power-bi

[diewalchin]

@amitchandak thank you for the quick reply!

Yes, I also thought about it. This means that I should unpivot the columns, which means that my column headers are then in one column and a second column contains the respective values, and the date column stays untouched, right? Am I assuming it right that you would then create a new column that contains the group names (if column_header = 'AB', then 'group1',...) so that this column can be then used as a slicer?

I have further two questions:

• Do you have experience if the unpivoting also works for direct query data? So are the data then in the unpivoted table continuously updating?
• What if I want to use 3 visuals that are influenced by 3 slicers. All three slicers have the groups and the three visuals are updating dependent on the slicer selection. Do I then have to create 3 unpivoted data or can I use just 1 unpivoted data table? When I apply the "interaction concept" by enabling or disabling a cross-filter, then one table would be appropriate, or?