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 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 个有效的解决方案.我根据自己的喜好给它们编号了.前两个是亚历克西斯奥尔森.据我从 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] )
)

如果您希望将 2nd 或 3rd 作为度量而不是计算列，则需要对其进行调整.

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屋！