LOOKUPVALUE: a bug or a feature

[Sergiy]

I've worked with Power BI for many years and used LOOKUPVALUE function blissfully unaware of its hidden pitfall.

It seems that this function is not always reliable. There are cases when LOOKUPVALUE can not produce a result due to the filters it isn't able to remove.

Here is a pbix file that demonstrates the problem (uploaded to Dropbox):

LookUpValue_Surprise.pbix 

I created three measures:

_projectName_LOOKUPVALUE_Wrong
_projectName_CALCULATE
_projectName_NO_CALCULATE

The second and third measures work as they should, while the first one (_projectName_LOOKUPVALUE_Wrong) can not produce a correct result when Programs[Program Name] filter is applied.

For some reason LOOKUPVALUE is not able to remove the filter from 'Programs' table. That's why the function produces incorrect result.

The picture below captures the issue.

To see the semantic model, please download pbix I provided.

My questions are:

Did I miss something that led to incorrect result? What?

Is it expected that LOOKUPVALUE not always return the correct result? 

Why in documentation there is nothing that warns you that the function has such a limitation and can not be blindly trusted? Really, why?

Am I supposed to submit a ticket to Support?

SQLBI has an article on this function: https://dax.guide/lookupvalue/ 

I do not know how they know that "Internally LOOKUPVALUE generates this equivalent syntax:"

MS Help remains silent regarding the highlighted piece.

If it's true and internally MS removes applied filters using the line: ALL ( <Table_Result_ColumName> )

then it explains everything.

But why they remove filters with this line?

Why not REMOVEFILTERS() ?

So, in my case ALL ( <Table_Result_ColumName> ) does not remove a filter from 'Programs' table.

[OwenAuger · Accepted solution]

Hi @Sergiy 

• First of all, I agree that Microsoft's LOOKUPVALUE documentation is severely lacking in describing how existing filters are handled. I had previously believed that it ignored all filters when carrying out itse search, but that is clearly not the case!
• If we rely on SQLBI's description of the function and the "equivalent syntax", then ALL ( <Table_Result_ColumnName> ) would remove all filters on the expanded table containing the result column.
• In your model, the expanded 'Projects' table is just the 'Projects table itself (since all related tables are on the many-side of 1:many relationships with 'Projects').
• So ALL ( 'Projects' ) removes all filters on the 'Projects' table itself. But it does not remove filters on ProjProg_Ids or Programs which can filter 'Projects' via the bidirectional relationship between ProjProg_Ids and Projects. It would have to internally apply ALLCROSSFILTERED ( 'Projects' ) in order to remove all filters that could affect 'Projects'.
• A possible rewriting of the LOOKUPVALUE measure in this case could be this (which is pretty awkward):

_projectName_LOOKUPVALUE_Fixed = 
VAR LookupProjectID =
    SELECTEDVALUE('Dependencies'[ProjectId])
RETURN
    CALCULATE (
        LOOKUPVALUE(
            Projects[Project Name], 
            Projects[Id], 
            LookupProjectID
        ),
        ALLCROSSFILTERED ( Projects )
    )

What would I take away from this?

• LOOKUPVALUE is a risky function to use at the best of times, as it does not ignore all filters that could impact the table containing the result column (specifically filters from tables on the many-side of bidirectional relationships).
• Microsoft's documentation is lacking and should be updated (I've just placed some feedback myself).
• Personally, I would avoid LOOKUPVALUE in favour of other methods such as the examples you created.
• It would be good to understand the source of SQLBI's "equivalent syntax". It might be worth placing feedback on either of these pages:
  • https://dax.guide/lookupvalue/ 
  • https://www.sqlbi.com/articles/understanding-lookupvalue/

[Sergiy]

@OwenAuger , thanks for your thorough comments.

I fully agree with your takeaway points.

Regarding this one:

• Microsoft's documentation is lacking and should be updated (I've just placed some feedback myself).

---

I incline to submit a ticket to Support.

The issue could be resolved either by updating the documentation as you suggested or by fixing the function.

I'd rather they fix it. There is no plausible explanation why this function does not cover all the cases. 

[Sergiy]

I incline to submit a ticket to Support.

I'd like to share the news I got from MS support team:

LOOKUPVALUE was created in the early days of DAX before bi-directional crossfiltering was invented. The function was implemented to ignore filters from tables related to the table of the lookup column but does not ignore filters from bi-directional crossfiltering which didn’t exist back then. The function was not updated to also ignore filters across bi-directional crossfiltering when the latter was invented. In this case, the presence of a filter on the Programs[Program Name] column affected the results of the function. Since this behavior has existed for years, there is a high probability of “breaking” many existing reports by returning different values if we were to update the implementation of the function.
 
Hence they have considered changing the document and as well they will check that this falls under BUG.

So I was assured that the documentation on LOOKUPVALUE function will be updated to shed light on its hidden details.

[lbendlin]

ALL(column) is equivalent to REMOVEFILTERS(column).

You should try to use TREATAS for filter context transfers rather than LOOKUPVALUE.   (As usual, you need to measure the actual performance/query plan when making a design decision).