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.
有没有办法使用类似于 (min(abs( DAX 中 excel 公式的一部分(因为它具有这些函数))在计算列中计算这个?这是否可能没有现有关系,或者我会每次我想更新此报告时都必须继续在 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?
非常感谢任何帮助.
推荐答案
在 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]
是 TableA 中的计划开始日期/时间列[Actual]
是 TableB 中的实际开始日期/时间列
[Planned]
is the Planned Start Date/time column in TableA[Actual]
is the Actual Start Date/Time column in TableB
根据您的型号更换.
如果每个表中没有事件列,请在过滤器函数中抑制该条件.
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屋!