Link column headings from table 1 to a parent category in table 2 for report filters

user_14054185 0
Viewed 62

Gurus,

I have 2 tables and i would link the column

Table 1

Name Process1 Process2 Process3 Process4

joe1     1        3         2      4

Joe2     0        2         4      2

joe3     3        2         2      2

I have a visual based on the above table. I have one more table

Table 2

Group     Process

ABC       Process1

ABC       Process2

XYZ       Process3

XYZ       Process4

I would like to set a filter on table 2 - group , so if the user clicks ABC only the process 1 and Process 2 shows up in the Visual of TABLE 1 values.

How do i make this happen?

currently i am struggling - I have looked at several options

  1. I am taking this excel file to powerBI and then taking the powerbi visual to powerapp.
  2. if i could get the link then i can use filter on page option in powerbi to filter based on GROUP from table 2 so my table1 visual changes based on selection
powerbi powerquery excel
1 Answers

You can achieve this by un-pivoting your "Table1" Data and using Matrix as a visual. Steps are give below-

Step-1: Go to Power Query window by selecting the "Transform Data" from Home menu.

Step-2: Select the "Name" column in table1 and apply Un pivot other columns as shown in the below image-

enter image description here

The output will be as below-

enter image description here

Step-3: Get back to report by clicking "Close & Apply" button.

Step-4: Create a many to many relation between your table1 and table2 using column Group-Attribute.

Step-5: Add a Matrix visual and add columns from Table1 as below-

enter image description here

Step-6: Create Slicer using Group column from Table2

Here is the output for selecting different gorup-

enter image description here

user_36523450
Related