Create relationship between two tables that don't have unique values without creating a third table

[OPS-MLTSD]

Hello,

I have a table (Clients) that looks like this:

Client ID	Starts Fiscal Year	Service Start Date	Exits Fiscal Year	Service Exit Date
1234	2020-2021	February 1, 2020	2021-2022	November 18, 2021
1243	2021-2022	January 10, 2022	NULL (service not ended yet)
1356	2020-2021	April 16, 2020	2021-2022	October 30, 2021
1367	2019-2020	January 18, 2019	2020-2021	December 1, 2020
1423	2021-2022	February 21, 2021	NULL

I have another table (Targets) that looks like this:

Business ID	Fiscal Year	Category	Proposed amount of people
44567	2021-2022	Client Starts	1300
44567	2021-2022	Client Exits	1000
44567	2020-2021	Client Starts	1200
44567	2020-2021	Client Exits	900
44567	2019-2020	Client Starts	800
44567	2019-2020	Client Exits	600
83726	2021-2022	Client Starts	1350

These two tables (Clients and Targets) are connected to another table, Business table (Clients table is connected to Business table via Client ID. This is an inactive relationship) and (Targets table is connected to Business table via Business ID)

I am trying to create a relationship between the Clients and Targets table via fiscal Year.

Now I am familiar with the concept of creating a unique fiscal year table and then connect it to both the Clients and Targets table.

Normally, this approach would suffice but the problem is, my Service Start fiscal year is different than my Exits Fiscal Year, that is why I have two different fiscal Year columns in my Clients table.

So my question is, how can I connect the Targets and Clients table so that I can do analysis such as compare actual “Client Starts” to targeted “Client Starts” for the fiscal year 2021-2022; or compare actual “Client Exits” to targeted “Client Exits” for the fiscal year 2020-2021?

Please note: Fiscal Year 2019-2020: January 1, 2019 to January 31, 2020

                     Fiscal Year 2020-2021: February 1, 2020 to January 31, 2021

                     Fiscal Year 2021-2022: February 1, 2021 to January 31, 2022

If someone could please help me figure this out, I would really appreciate it!  

[Anonymous]

Hi @OPS-MLTSD 

When you use a column in table A to compare the two columns in table B , USERELATIONSHIP() can help you . As we all know , there can only one active relationship between two tables, if you want to create two relationships between two tables , you need use USERELATIONSHIP(). I have done a case related to USERELATIONSHIP() before, you can refer to my case.

https://community.powerbi.com/t5/Desktop/Show-Purchased-Returned-Date-using-Calendar-Table/td-p/2299...

Best Regard

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[OPS-MLTSD]

@Anonymous thanks for sharing the info, I am a bit confused, how do I createa relationship between the Clients and Targets table in the first place? As I said, the only similar column between these two tables are the Fiscal Year columns but they do not contain unique values. Thanks, appreciate your help 🙂

[aj1973]

Hi @OPS-MLTSD 

In both of your tables you have Business ID and Fiscal Year Columns, Concatenate the 2 columns in both tables so you can have BusinessID-Fiscal Year then join both tables through these newly added columns

[OPS-MLTSD]

It seems that I might have copied the table columns wrong, thanks for finding the mistake 🙂 I fixed it. Business ID is only present in the Targets table and Business Table, it is not present in the Clients table. 

Thank you