DAX lookup first non blank value in unrelated table

3
votes

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.

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

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

Sample data file:

DAX lookup top 1 value.pbix

Comment to accepted answer

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
)

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.

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.

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

How to check in Daxstudio which DAX query has better performance?

Update seeking further improvements

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 )
)
4
powerbidaxdaxstudio
This might help: stackoverflow.com/questions/52525377/… – Alexis Olson
For the benefit of other readers, I am cross-referencing here the suggestions I have made about bounty negotiations in questions. The summary points I have made are: (a) bounty negotiating is not of interest to most readers, who do not sign in; (b) on this platform, bounty negotations are sometimes used as a way to obtain free work, and this is normally frowned upon here; (c) once such a bounty has been paid, the outdated offer is rarely removed from where it has been added. – halfer
(I will continue the conversation on the other thread as required). – halfer
Please don’t put meta notices in your question posts. You can use a comment or the bounty notice field. – Martijn Pieters

4 Answers

1
votes

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

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 )

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.

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.

Edit:

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.

1
votes

Try this:

Column =
VAR row_id = ThisTable[id] 
VAR ret_val =
    SELECTCOLUMNS (
        TOPN (
            1;
            CALCULATETABLE (
                SearchTable;
                SearchTable[id] = row_id;
                SearchTable[name] <> BLANK ()
            )
        );
        "name"; SearchTable[name]
    )
RETURN
    ret_val
1
votes

I think I solved this issue: Measure0 is measure Measure1 is Calculated Column measure Apple has no Category because it has 3 categories and can’t pick one, so it returns blank

Measure0 =
FIRSTNONBLANK (
    SearchTable[Category],
    CALCULATE (
        MAX ( SearchTable[Product] ),
        TREATAS ( VALUES ( ThisTable[product] ), SearchTable[Product] )
    )
)

Measure1 =
CALCULATE (
    FIRSTNONBLANK (
        SearchTable[Category],
        CALCULATE (
            MAX ( SearchTable[Product] ),
            TREATAS ( VALUES ( ThisTable[product] ), SearchTable[Product] )
        )
    )
)
0
votes

There is a FIRSTNONBLANK(column, expression) function. Doesn't it help?