Forum Discussion
One to Many RelationShip
Hi, I am having an issue filtering or connecting two tables, I believe that I need a bridging table to connect each table, but I am unsure on how to set one up. Both datasets contain survey data, and are in "long" format i.e. each contains a unique customer ID, a column with whom they are reporting to. So the first table is like:
| Unique Customer ID | Reporting Managers |
| 1 | Mgr1 |
| 2 | Mgr2, Mgr3 |
| 3 | Mgr1, Mgr3, Mgr4 |
| 4 | Mgr2,Mgr4 |
| 5 | Mgr1,Mgr2,Mgr3,Mgr4 |
and then the second table is like
| Mgr1 | Manager One |
| Mgr2 | Manager two |
| Mgr3 | Manager Three |
| Mgr4 | Manager Four |
| Mgr5 | Manager Five |
Now, I want to have a slicer with dropdown of Managers who are mentioned in Table 1 (Mgr 1 - Mgr4) and dropdown shouldn't show Mgr5 as No one had mentioned. I am stuck with how to do relationship as Reporting Manager column is a multi select column. Dropdown should only Show Mgr 1, Mgr2, Mgr3, Mgr4 ignoring Mgr5.
Thanks in advance and please guide me how to get this report?
Agree with AbhinavJoshi .
You can generate a new table contains the manager ID if you don't want it to affect the count.
Here're the steps:
1.Duplicate the table.
2.Remove other columns.
3.Split the column.
4.Trim to remove the spaces.
5.Go back to power bi desktop, create a measure to make the filtering successful.
Measure = IF(CONTAINSSTRING(MAX('Table'[Reporting Managers]),SELECTEDVALUE('Table (2)'[Reporting Managers])),1)6.Put the measure into the visual-level filters and set up show items when the value is 1.
7.Here's the result. When you select Magr2 in the slicer, the table visual only displays the reporting managers contains Magr2.
Note: There's no relationship between the new table and the main table.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
HI Nomji , I am not sure how you are linking your two tables and what data do you have at the second table other that manager rank. One option is to split the column in rows from table one using delimeter.
If i split by comma and in rows how will it affect count wise? Can you elaborate more in detail please. In second table I have only Manager Id and name. In table 1 there are few other columns like hours, budget and other info
Agree with AbhinavJoshi .
You can generate a new table contains the manager ID if you don't want it to affect the count.
Here're the steps:
1.Duplicate the table.
2.Remove other columns.
3.Split the column.
4.Trim to remove the spaces.
5.Go back to power bi desktop, create a measure to make the filtering successful.
Measure = IF(CONTAINSSTRING(MAX('Table'[Reporting Managers]),SELECTEDVALUE('Table (2)'[Reporting Managers])),1)6.Put the measure into the visual-level filters and set up show items when the value is 1.
7.Here's the result. When you select Magr2 in the slicer, the table visual only displays the reporting managers contains Magr2.
Note: There's no relationship between the new table and the main table.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
