Forum Discussion

OPS-MLTSD's avatar
OPS-MLTSD
Icon for Post Patron rankPost Patron
4 years ago
Solved

Create relationship between two tables that do not have unique values

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 idstart fiscal yearservice start dateservice end fiscal yearend datelocation IDproject number
123452020-2021april 12, 20202021-2022April 12, 20213889A1
145622020-2021may 1, 20202021-2022may 1, 20213889A2
156782021-2022April 29, 20212022-2023April 20, 20225667A2
167852021-2022May 9, 20212022-2023May 1, 202258991B1
187652019-2022July 1, 20192022-2023April 2, 202258991B3

 

I have  a targets table that looks like this:

 

Business IDTarget Fiscal YearTarget CategoryProposed amount of peopleLocation IDProject Number
445672021-2022Client Starts13003889A1
445672021-2022Client Exits10003889A1
445672020-2021Client Starts12005667A2
445672020-2021Client Exits9005667A2
445672019-2020Client Starts8003889A1
445672019-2020Client Exits6003889A3
837262021-2022Client Starts 135058991B1

 

and I have a location table that looks like this:

location IDlocation Name
58991B58991B
3889A3889A
5667A5667A

 

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
Need Help
  • sevenhills's avatar
    sevenhills
    4 years ago

    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. 

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

     can you send picture of your raltions ?

  • sevenhills's avatar
    sevenhills
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      OPS-MLTSD
      Icon for Post Patron rankPost Patron
      4 years ago

      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's avatar
        sevenhills
        Icon for Super User rankSuper User
        4 years ago

        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.