DAX最接近的值匹配，没有任何关系 [英] DAX closest value match with no relationship

问题描述

我正在尝试将报告从Excel迁移到Power BI，希望当我不熟悉DAX时有人可以帮助我.

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.

我有两个表，一个(称为表A)包含一列事件的计划开始日期/时间，而另一个包含相同事件的实际开始日期/时间.计划的开始时间与实际的开始时间通常只有几分钟的时间.

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.

我需要将表B中最接近的实际开始日期/时间与表A中计划的开始日期/时间进行匹配.

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.

如果我可以找到最接近的实际开始时间并将其拉入表A，则可以从中创建一个关系.

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

在Excel中，我将使用如下数组公式来执行此操作:(这里我只是假设所有内容都在每个表的A列中)

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)}

我在网上找到了以下DAX代码，但是即使有更高的更接近的值，它也只会返回下一个最小值.

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] ) )
)
)

如果我构建了一个日期/时间表，其中包含我的数据所涵盖的日期范围的每一分钟(大约2年)，但我也想尝试一种解决方法，但正如我所说的那样，到DAX，还无法弄清楚.

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.

是否可以使用类似于(DAX中excel公式的min(abs(部分)(因为它具有这些功能))的某种方式在计算所得的列中进行计算?这是否可能没有现有的关系，或者我会这样做吗?每次我要更新此报告时，都必须继续在Excel中完成这部分工作吗?

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.

推荐答案

在Planned表中创建一个计算列，将其命名为ActualClosestDate并使用以下表达式:

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] )
        )
    )

位置:

• [Planned]是表A中的计划的开始日期/时间"列
• [Actual]是表B中的实际开始日期/时间"列

• [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.

如果每个表中没有一个Event列，则过滤条件起作用.

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最接近的值匹配，没有任何关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！