Create relationship between two tables that do not have unique values

[OPS-MLTSD]

Hello,

I have 3 tables - clients, targets, and location.

I am trying to count how many clients started and finished services within a certain target fiscal year and in a certain target category. 

The clients and targets table do not have a relationship but they are both connected to the location table via location ID. 

I have a table called clients table that looks like this:

client id	start fiscal year	service start date	service end fiscal year	end date	location ID	project number
12345	2020-2021	april 12, 2020	2021-2022	April 12, 2021	3889A	1
14562	2020-2021	may 1, 2020	2021-2022	may 1, 2021	3889A	2
15678	2021-2022	April 29, 2021	2022-2023	April 20, 2022	5667A	2
16785	2021-2022	May 9, 2021	2022-2023	May 1, 2022	58991B	1
18765	2019-2022	July 1, 2019	2022-2023	April 2, 2022	58991B	3

I have  a targets table that looks like this:

Business ID	Target Fiscal Year	Target Category	Proposed amount of people	Location ID	Project Number
44567	2021-2022	Client Starts	1300	3889A	1
44567	2021-2022	Client Exits	1000	3889A	1
44567	2020-2021	Client Starts	1200	5667A	2
44567	2020-2021	Client Exits	900	5667A	2
44567	2019-2020	Client Starts	800	3889A	1
44567	2019-2020	Client Exits	600	3889A	3
83726	2021-2022	Client Starts	1350	58991B	1

and I have a location table that looks like this:

location ID	location Name
58991B	58991B
3889A	3889A
5667A	5667A

I tried to create a relationship between clients table and targets table using the location ID but since both these columns do not contain unique values, it did not work. Then I tried to use the USERELATIONSHIP function to create a relationship between the two tables using the dax below:

intake test = CALCULATE(

COUNT(clients[client id]),

USERELATIONSHIP(target[location ID],clients[location ID])

)

but I get this error message: USERELATIONSHIP function can only use the two columns references participating in relationship.

If someone could please help me figure out how I can create a relationship between these two tables, that would be very helpful.

thank you

[sevenhills]

I tried creating relationship like this with your data samples

Note: Client is a unique list of Client_id, I created and not sure whether you need it or not.

Try these, as I was NOT sure from the post, which ones you need

intake test = COUNT(client[client id])

Testing Client in Targets = 

CALCULATE( COUNT(Client[client id])
  , CROSSFILTER(Targets[Location ID],Location[location ID], BOTH) 
)

Clients Count = 
var _l = SELECTEDVALUE(Targets[Location ID])
return CALCULATE( count(Client_Locations[client id]), FILTER(Client_Locations, Client_Locations[location ID] = _l))

Note: USERELATIONSHIP may not fit your scenario. https://docs.microsoft.com/en-us/dax/userelationship-function-dax 

[OPS-MLTSD]

@sevenhills 

thank you so much for the information.

I tried to recreate your data and I am still not able to establish a relationship between the clients and targets table. 
I was wondering, would it be possible to modify this dax like this? Because I need to know for example clients starts for a particular target fiscal year, can you help me modify my dax below?

test measure = 

CALCULATE( COUNT(clients[client id]),

FILTER(target,target[Target Category]="Client starts"),
clients[Start fisca year] = targets[target fiscal year] AND clients[Location id] = targets [location ID]

CROSSFILTER(target[location ID],location[Location ID], BOTH)

I hope this makes sense to you! Please let me know if you need some more clarifications 

[sevenhills]

I am not sure what do you mean by "I tried to recreate your data and I am still not able to establish a relationship between the clients and targets table. "

----------------------------------------------------------

Also, Is this what you are looking? based on my modeling diagram above ... 

Testing Client in Targets 2 = 
var _Target_FY = SELECTEDVALUE(Targets[Target Fiscal Year]) 

RETURN CALCULATE( COUNT(Client_Locations[client id])
  , Filter ( Targets, Targets[Target Category] = "Client Starts")
  , CROSSFILTER(Targets[Location ID],Location[location ID], BOTH) 
  , Filter(Client_Locations, Client_Locations[start fiscal year] = _Target_FY)
)

----------------------------------------------------------

Could you share your data screens and model diagram? it is tough to visualize.

[OPS-MLTSD]

@sevenhills  thank you for the resource, you have actually modelled my data correctly. What I am trying to fugure out is - for example:
how many clients started service within the target fiscal year 2021-2022?

Normally, I would be able to get the data very easily by doing this:
intake test = COUNT(client[client id]) and then filtering for the year 2021-2022 using the column "start fiscal year" from my clients table. However, since target fiscal year is different that actual service start fiscal year, I want to be able to use the column "Target Fiscal Year" from the targets table to filter for 2021-2022 client start numbers. I hope this explanation make sense! Please let me know if you have any questions, thank you so much!

[sevenhills · Accepted solution]

one more attempt... see which of these measures work for your scenario.

Measure 3 = 
var _l = SELECTEDVALUE(Targets[Location ID])
var _FY = SELECTEDVALUE(Targets[Target Fiscal Year])
RETURN CALCULATE(
        count( Client_Locations[client id]) 
        , Filter(Targets, Targets[Target Category] = "Client Starts" )
        , Client_Locations[location ID] = _l
        , Client_Locations[start fiscal year] = _FY
)

Measure = 
CALCULATE(
        count( Client_Locations[client id]) 
        , Filter(Targets, Targets[Target Category] = "Client Starts")
        , TREATAS (
            SUMMARIZE(Targets, Targets[Location ID], Targets[Target Fiscal Year])
            ,  Client_Locations[location ID], Client_Locations[start fiscal year]
            )
)

You are doing is common scenario when you have multiple fact tables and getting related counts based on business needs. 

[OPS-MLTSD]

@sevenhills 

thank you so much! they both worked for me! Just wondering, what is the function of the TREATAS dax? this is the first time I am seeing this one.

You are right, it was a matter of multiple fact tables. To avoid that issue, I created a fiscal year dim table which I connected to both my clients and targets table to be able to filter for the data.

[sevenhills]

Glad it worked. Having lookup / common-unique / dim tables in the model always better and easy.

You can read about TREATAS here: https://dax.guide/treatas/ 

In my own terms, by using TREATAS basically saying the columns (data) we are providing from one table is same as in other table columns. 

[OPS-MLTSD]

thanks so much for the resource!

[Anonymous]

 can you send picture of your raltions ?