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 catego...
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. 

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.

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

        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!