DAX查找不相关表中的第一个非空白值 [英] DAX lookup first non blank value in unrelated table

问题描述

如何在不相关表中查找第一个非空白值。我想要一个等效项：

How to lookup the first non blank value in unrelated table. I want an equivalent of:

• SQL 外部适用（选择前1个...）或


• Excel VLOOKUP 。

• SQL outer apply (select top 1 ...) or
• Excel VLOOKUP.

该代码中使用的DAX LOOKUPVALUE 函数不起作用。

The DAX LOOKUPVALUE function as used in that code does not work.

LOOKUPVALUE(
      SearchTable[name]
    , SearchTable[id] -- how to get the fist value, if here are multiple id?
    , ThisTable[id]
    )

DAX LOOKUPVALUE 函数可以正常工作，如果结果仅返回一个唯一值。如果在搜索表中找到一个键的多个值，函数 LOOKUPVALUE 返回错误消息：

DAX LOOKUPVALUE function works fine, if only one unique value is returned as a result. The function LOOKUPVALUE returns error message, if multiple values are found in the search table for one key:

提供了多个值的表，其中单个值是预期的
。

A table of multiple values was supplied where a single value was expected.

我不希望出现错误消息，我对前1个结果感到满意。

I do not want error message, I am happy with the top 1 result.

示例数据文件：

DAX查找前1个值。pbix

我最喜欢Alexis Olson的第二种方法，尽管我更喜欢 VAR 而不是 EARLIER 变体：

I favor Alexis Olson's second approach the most, though I prefer VAR to EARLIER variation:

MyLookup = 
var LookupKey = ThisTable[Product]
return
CALCULATE (
    MAX ( SearchTable[Category] ),
    SearchTable[Product] = LookupKey
)

我接受了它，尽管我强烈感觉这不是可能存在的最佳答案。该函数返回 MAX 而不是 TOP 1 ，这显然是额外的工作。

I accepted it though I have a strong feeling that this is not the best answer which may exist. The function returns MAX instead of TOP 1 which is obviously additional effort.

示例数据现在包含4个有效解决方案。我已根据自己的喜好为它们编号。前两个是亚历克西斯·奥尔森（Alexis Olson）创作的。据我从DAX Studio可以了解到的那样，这两种解决方案都非常快且执行计划简单。第二种解决方案是唯一仅执行两次表扫描的解决方案（所有其他方法均进行3次扫描）。第三种解决方案具有复杂的执行计划，并且在第三次扫描期间，它将遍历整个搜索表，而不管它是否是一百万行。

The sample data now contains 4 working solutions. I have numbered them according to my preferences. The first two are by Alexis Olson. As far as I could learn from DAX Studio, both solutions are similarly fast and have a simple execution plan. The second solution is the only solution that performs just two table scans (all other approaches have 3 scans). The third solution has a complicated execution plan, and during the third scan it passes through the entire search table, regardless of whether it is a million rows.

此问题有一个后续问题，我尝试在其中比较Dax Studio中的查询：

This question has a follow up question where I try to compare the queries in Dax Studio:

如何在Daxstudio中检查哪个DAX查询的性能更好？

我想知道是否有可能实现比目前的成功答案更短的查询计划。获胜的代码将在 DaxStudio 中进行评估，并且其查询计划应短于物理行和逻辑行的20行

I wonder if it is possible to achieve a shorter query plan then the current winning answer. The winning code will be evaluated in DaxStudio and should have shorter query plan then total 20 line of both physical and logical lines of query plan.

-- Bounty code
EVALUATE
ADDCOLUMNS (
    VALUES ( ThisTable[Product] ),
    "FilterLookup",
    VAR LookupKey = ThisTable[Product]
    RETURN
        CALCULATE ( MAX ( SearchTable[Category] ), SearchTable[Product] = LookupKey )
)

推荐答案

有很多方法可以做这个。以下是我想到的前三项：

There are plenty of ways to do this. Here are the top three that come to mind:

此最简单的功能是查找功能，但仅当 SearchTable 。

This simplest is a lookup function but only works if there is a single category associated with each product in the SearchTable.

LookupFunction =
LOOKUPVALUE ( SearchTable[Category], SearchTable[Product], ThisTable[Product] )

以下内容仍应适用于多种分类的产品：

The following will should still work with multiply categorized products:

MaxxLookup =
MAXX (
    FILTER ( SearchTable, SearchTable[Product] = ThisTable[Product] ),
    SearchTable[Category]
)

CaclculateMaxLookup =
CALCULATE (
    MAX ( SearchTable[Category] ),
    SearchTable[Product] = EARLIER ( ThisTable[Product] )
)

如果要使用第二或第三作为度量值而不是计算列，则需要对其进行调整。

If you want 2nd or 3rd as a measure instead of a calculated column, they'd need to be adjusted.

注意：以上工作内容作为计算列。如果希望它们既用作计算列又用作度量，则按如下所示对其进行调整：

Note: The above work fine as calculated columns. If you want them to work as both calculated columns and as measures then adjust them as follows:

LookupMeasure =
LOOKUPVALUE (
    SearchTable[Category],
    SearchTable[Product], CALCULATE ( SELECTEDVALUE ( ThisTable[Product] ) )
)

MaxxMeasure=
VAR CurrProduct = CALCULATE ( SELECTEDVALUE ( ThisTable[Product] ) )
RETURN
    MAXX (
        FILTER ( SearchTable, SearchTable[Product] = CurrProduct ),
        SearchTable[Category]
    )

CaclculateMaxMeasure =
VAR CurrProduct = CALCULATE ( SELECTEDVALUE ( ThisTable[Product] ) )
RETURN
    CALCULATE ( MAX ( SearchTable[Category] ), SearchTable[Product] = CurrProduct )

在重新编写计算列时，有行上下文可以告诉您要使用哪种产品。编写度量时，需要从 ThisTable [Product] 列中指定要使用的值。

The difference is that when you're writing a calculated column, there is row context to tell you which product to use. When you write a measure, you need to specify which value from the column ThisTable[Product] you intend to use.

注意：如果您的过滤器上下文中有多个产品，并且您希望度量值查找最大产品的类别，则使用 MAX 而不是 SELECTEDVALUE 。如果有多个值，则后者将返回空白。

Note: If there are multiple products in your filter context and you want the measure to look up the category of the maximal one, then use MAX instead of SELECTEDVALUE. The latter will return a blank if there are multiple values.

编辑：

以上公式适用于提供的原始数据集。但是，如果 SearchTable 中与单个产品相关的多个类别，则 LOOKUPVALUE 函数将中断（因为它期望只包含一个值），则您将需要使用 MAX 或 MAXX 版本之一。

The above formulas worked for the original set of data provided. However, if there are multiple categories associated with a single product in the SearchTable then the LOOKUPVALUE function will break (since it expects only a single value) and you will need to use one of the MAX or MAXX versions instead.

这篇关于DAX查找不相关表中的第一个非空白值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！