Joining tables based on two different fields

[shanebo3239]

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.  As such I have two different tables.  I'll just call them LMS and CSF.

In LMS, I have a StartDate and an EndDate.  I also have a LocationID and a ProcessId.

In CSF, I have a DateSubmitted and a LocationID.

I need to determine LMS.ProcessID based on two things 1) Whether CSF.DateSubmitted falls between LMS.StartDate and LMS.EndDate and 2) LocationId.  I could theoretically have 15 customers submit a survey within a range, so without the second part of the equation (the LocationID), I'm sunk.

Is there any way to accomplish this in Power BI? 

Thank you!

[v-ljerr-msft]

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.

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

Regards

[shanebo3239]

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.

[BetterCallFrank]

something along the lines of

FILTER(
  LMS,
  CSF[DateSubmitted] >= LMS[StartDate] && 
  CSF[DateSubmitted] <= LMS[EndDate] &&
  CSF[LocationID] = LMS[LocationID] 
)

 shoud work for you 🙂

If you need more please post some screenshots of your data and relationships

HTH,

Frank