How to get information from another Power BI table

Question

I have two tables, one where are the incidents created and the date (table1), and another where are the people who were on duty on those dates (table2).

Table1

Table2

what I am looking for is to create in my table1 the column called "persona crea" in which I want to bring the personnel that was on duty (from table2) on that date that the incident is created. the tables have nothing in common to relate them. I make this simple example because my powerbi data has a lot of information. I hope you can help me, I tried the lookupvalue but it works when the tables have something in common, but this is not the case.

enter image description here

Answer 1

You may need to create a helper column to generate the desired relationship.

The time stamps in fecha inicio seem to indicate that there is only one person per shift and that the shifts start either at midnight or at noon. If that is an established pattern, you can create a helper column in the Table1 that uses a calculation based on fecha crea. The formula would need to round down the value in fecha crea to previous noon or midnight. With that, you have the lookup value you need for a join or a relationship.

The custom column could be added with Power Query or with Dax, depending on where you get the data from and what method you prefer.

Conceptually, in Power Query, I'd use this approach:

• get the hour value for fecha crea
• if the hour value is less than 12, return a 0, otherwise return a 12
• remove the time from the fecha crea value
• add a time stamp using the hour value you just established.