How to connect data with exact time in one dataset to a time range in other dataset

[TessGaston]

Hello,
please could somebody help me with my problem?

I have many XML files with call logs and one excel file with attendance of operators.

XML file contains ID of the phone, date of the call and some other stuff.

Excel file contains ID of the phone, date of the attendance of the operator, time from, time to (when the operator started and finished work that day) and the operator's name.

What I need is to add a new column in the XML files in Power BI and type in the name of the operator who managed the call. So it should search somehow the ID of the phone with the date and then check if the time is older or equal to the time "from" and simultaneously that the time is younger or equal to the time "to", if everything meet the conditions, than it should type in the column "name" the name from the row which met the conditions.

Is that possible in Power BI somehow? I hope it is because I need to automatize this process fullfully (with no manual step like preparing CSV files) so I would be able to create reports from the data.

Thank You!!!

Tereza

[v-cherch-msft · Accepted solution]

Hi @TessGaston

You may use SELECTEDVALUE Function. Show you an example as below:

Measure =
MINX (
    FILTER (
        Excel,
        SELECTEDVALUE ( XML[Time] ) > Excel[Start]
            && SELECTEDVALUE ( XML[Time] ) < Excel[End]
    ),
    Excel[Name]
)

Column =
MINX (
    FILTER ( Excel, XML[Time] > Excel[Start] && XML[Time] < Excel[End] ),
    Excel[Name]
)

Regards,

Cherie