Forum Discussion

Sergiy's avatar
Sergiy
Resolver II
2 years ago
Solved

LOOKUPVALUE: a bug or a feature

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 ...
Need Help
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    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:

     

Sergiy's avatar
Sergiy
Resolver II
2 years ago

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's avatar
Sergiy
Resolver II
2 years ago
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.