One to Many RelationShip

[Nomji]

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?

[AbhinavJoshi]

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. 

[Nomji]

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

[Anonymous · Accepted solution]

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.

[Nomji]

Thanks Stephen!!! For relationship I have included unique column and am able to receive count.