DAX closest value match with no relationship

Question

I'm trying to migrate a report from Excel into Power BI and I'm hoping someone can help me as I'm new to DAX.

I have two tables and one (let's call it table A) contains a column of planned start Date/Times for events while the other contains the actual start Date/Times of the same events. There is usually only a few minutes difference between the planned and actual start times.

I need to match the closest actual start Date/Time from Table B to the planned start Date/Times in table A.

There are no existing columns that I can use to create a relationship between the two tables.

If I can find the closest actual start time and pull it into Table A then I can create a relationship from that.

In Excel I would do this with an array formula such as this: (here I'm just assuming everything is in column A of each table)

{=Index(TableB!A:A,match(min(abs(TableB!A:A-TableA!A1)),abs(TableB!:A:A-TableA!A1),0),1)}

I have found the following DAX code online but it will only return the next lowest value even if there is a closer value which is higher.

If (
Hasonevalue ( TableA[A] ),
Calculate (
Max ( TableB[A] ),
Filter ( TableB, TableB[A] <= Values ( TableA[A] ) )
)
)

I've also tried to figure out a way to do this if I build a date/time table which contains every minute of the date range that my data covers (about 2 years) at but as I said I'm new to DAX and haven't been able to figure it out.

Is there any way to use something similar to the (min(abs( part of the excel formula in DAX (as it has these functions) to calculate this in a calculated column? Is this possible without an existing relationship or will I have to continue to do this part of the work in Excel every time I want to update this report?

Any help greatly appreciated.

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-11-23T19:10:29

Create a calculated column in the Planned table, call it ActualClosestDate and use this expression:

ActualClosestDate =
    IF (
        DATEDIFF (
            CALCULATE (
                MAX ( TableB[Actual] ),
                FILTER ( TableB, [Planned] >= [Actual] && TableA[Event] = TableB[Event] )
            ),
            [Planned],
            SECOND
        )
            < DATEDIFF (
                [Planned],
                CALCULATE (
                    MIN ( TableB[Actual] ),
                    FILTER ( TableB, [Planned] <= [Actual] && TableA[Event] = TableB[Event] )
                ),
                SECOND
            ),
        CALCULATE (
            MAX ( TableB[Actual] ),
            FILTER ( TableB, [Planned] >= [Actual] && TableA[Event] = TableB[Event] )
        ),
        CALCULATE (
            MIN ( TableB[Actual] ),
            FILTER ( TableB, [Planned] <= [Actual] && TableA[Event] = TableB[Event] )
        )
    )

Where:

[Planned] is the Planned Start Date/time column in TableA
[Actual] is the Actual Start Date/Time column in TableB

Replace according to your model.

If you don't have a Event column in each table supress that condition in the filters functions.

UPDATE: Calculating three different columns could improve performance instead of perform the calculation in one expression.

BeforePlanned =
DATEDIFF (
    CALCULATE (
        MAX ( TableB[Actual] ),
        FILTER ( TableB, [Planned] >= [Actual] && TableA[Event] = TableB[Event] )
    ),
    [Planned],
    SECOND
)

AfterPlanned =
DATEDIFF (
    [Planned],
    CALCULATE (
        MIN ( TableB[Actual] ),
        FILTER ( TableB, [Planned] <= [Actual] && TableA[Event] = TableB[Event] )
    ),
    SECOND
)

ActualClosestDate =
IF (
    [BeforePlanned] < [AfterPlanned],
    CALCULATE (
        MAX ( TableB[Actual] ),
        FILTER ( TableB, [Planned] >= [Actual] && TableA[Event] = TableB[Event] )
    ),
    CALCULATE (
        MIN ( TableB[Actual] ),
        FILTER ( TableB, [Planned] <= [Actual] && TableA[Event] = TableB[Event] )
    )
)

You could even split it in more columns, i.e. a column to get the previous actual date and a column to get the next actual date then you just need:

ActualClosestDate =
IF ( [BeforePlanned] < [AfterPlanned], [PreviousActualDate], [NextActualDate] )

Let me know if this helps.

DAX closest value match with no relationship

1 Answers