Forum Discussion
Power BI Measure versus column
First Query : I have two tables for client requirement as attached in the image, when Table 1 factor measure for example "Mach" is selected below, the table2 should now filter only for Mach from "Factor" Column and display the line trend as shown in the image only for Mach .
Second Query : Table 2 has Factors bucketed into Factor Sets , I would like to have that as a slicer in my Table1 to select any sets and only those set related factors appear on the table1 format.
First, you need to unpivot Table1 to have a structure similar to Table2. This can be done using Power Query in Power BI.
Go to Power Query Editor.
Select the columns Elite, Maci, Lopf, Yutp, Klopw.
Right-click and choose "Unpivot Columns".
This will transform Table1 into a format with columns id, date, Factor, and Value.Add a slicer to your report using the Factor column from the unpivoted Table1
Use the slicer to filter Table2. When a factor is selected in the slicer, Table2 will be filtered to show only the rows where the Factor matches the selected value.
Use Factor Sets as a slicer to filter factors in Table1
Create a new table that lists the unique Factor Sets and their corresponding Factors.
plaintext
Factor Set | Factor
-------------------
1 | Elite
2 | Mach
1 | Lopfg
1 | Yutpr
1 | KlopwEnsure there is a relationship between this new Factor Set table and Table2 based on the Factor column.
Add a slicer to your report using the Factor Set column from the new Factor Set table.
Use the slicer to filter the unpivoted Table1. When a factor set is selected, only the factors belonging to that set will be displayed in Table1.
Hi Chaitra156j ,
As bhanu_gautam stated, you need to unpivot Table1 using Power query editor.
And in order to filter Table 1 via Table 2, you need to have a relationship between Table 1 and Table 2.
Unpivoted T1
According to the given data, the ID column in both tables creates Many-to-many relationship which wont help much with the filtering here. Therefore, you need a bridge table that would connect both the tables.You can create this table by pulling ID and Value columns from the unpovited table and group by ID. This will establish a relationship between the 2 tables and you can filter using Factor from Table 2.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Thanks
5 Replies
First, you need to unpivot Table1 to have a structure similar to Table2. This can be done using Power Query in Power BI.
Go to Power Query Editor.
Select the columns Elite, Maci, Lopf, Yutp, Klopw.
Right-click and choose "Unpivot Columns".
This will transform Table1 into a format with columns id, date, Factor, and Value.Add a slicer to your report using the Factor column from the unpivoted Table1
Use the slicer to filter Table2. When a factor is selected in the slicer, Table2 will be filtered to show only the rows where the Factor matches the selected value.
Use Factor Sets as a slicer to filter factors in Table1
Create a new table that lists the unique Factor Sets and their corresponding Factors.
plaintext
Factor Set | Factor
-------------------
1 | Elite
2 | Mach
1 | Lopfg
1 | Yutpr
1 | KlopwEnsure there is a relationship between this new Factor Set table and Table2 based on the Factor column.
Add a slicer to your report using the Factor Set column from the new Factor Set table.
Use the slicer to filter the unpivoted Table1. When a factor set is selected, only the factors belonging to that set will be displayed in Table1.
Hi Chaitra156j ,
As bhanu_gautam stated, you need to unpivot Table1 using Power query editor.
And in order to filter Table 1 via Table 2, you need to have a relationship between Table 1 and Table 2.
Unpivoted T1
According to the given data, the ID column in both tables creates Many-to-many relationship which wont help much with the filtering here. Therefore, you need a bridge table that would connect both the tables.You can create this table by pulling ID and Value columns from the unpovited table and group by ID. This will establish a relationship between the 2 tables and you can filter using Factor from Table 2.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
ThanksHi Chaitra156j,
May i know has your issue been resolved? If the response provided by the Anonymous, bhanu_gautam, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.If yes, kindly accept the useful reply as a solution and give us Kudos. It would be appreciated.
Thank you for your understanding!
Hi Chaitra156j,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hi Chaitra156j,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
