Forum Discussion

shanebo3239's avatar
shanebo3239
Icon for Helper I rankHelper I
9 years ago

Joining tables based on two different fields

I have an organization that has two systems, one is a lobby management system that gives out tickets (kind of like a "take a number" system).  The second system is a customer survey feedback system. ...
Need Help
v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi shanebo3239,

 

Is there any relationship between your LMS table and CSF table?

 

According to your description, you should be able to use the "LocationID" column to create a relationship between the two tables, then use RELATED Function (DAX) to create a calculate column to determine whether CSF.DateSubmitted falls between LMS.StartDate and LMS.EndDate with the equation of LocationID.

 

For more details about how to create and manage relationships in Power BI Desktop, you can refer to this article. And the formula below to create the calculate column is for your reference.:smileyhappy:

IsSubmitBetweenTimeRange =
IF (
    RELATED ( CSF[DateSubmitted] ) >= LMS[StartDate]
        && RELATED ( CSF[DateSubmitted] ) <= LMS[EndDate],
    TRUE,
    FALSE
)

 

Regards

  • shanebo3239's avatar
    shanebo3239
    Icon for Helper I rankHelper I
    9 years ago

    Thank you for your help!

     

    Unfortunately I am still struggling.  It is probably because I left out one bit of info that now seems important.  There's another table at play here.  The CSF table has a "StationId" field.  The LMS table has a "LocationId" field.  These two fields refer to the same thing, but not in the exact same way.

     

    So, I created a central table between them to create the relationship.  Let's consider that to be LocationMaps.

     

    So, I've got something in that table like

     

    primary_key  |  locationid  |  stationid

    1 | 47 | 106

     

    Then i created a relationship between the CSF table and the LocationMaps table on LocationMaps.stationId.  Then created a relationship between the LMS table and the LocationMaps table on LocationMaps.locationid. 

     

    Now when I try to use "RELATED" the only option that works is the LocationMaps table from either of the other tables.  I cant create a calculated field in the CSF table and say "hey, show me all of the LMS.ProcessIds". 

     

    I hope this makes some semblance of sense and I really appreciate the feedback.