topic Re: Lookupvalue returning the complete column in blanks in DAX Commands and Tips

Lookupvalue returning the complete column in blanks

campelliann — Thu, 25 Feb 2021 18:21:58 GMT

Hi,

So I have this fact table which unrelated to a lookup table, and I am extracting values from the latter using the lookupvalue.

The thing is that I am getting all blanks, and I have no idea why. Some Ids are only in the fact table, but I am using 0 as the alternative result. What is going on here?

Ideas?

Thanks!

Re: Lookupvalue returning the complete column in blanks

dedelman_clng — Thu, 25 Feb 2021 21:28:05 GMT

Hi campelliann - can you provide the code you are using, along with a sample of your data? Hard to know what is happening without specifics.

Or better yet if you can share a copy of your pbix with sensitive data removed. Use OneDrive, Googledrive, dropbox, etc to share a link.

David

Re: Lookupvalue returning the complete column in blanks

campelliann — Thu, 25 Feb 2021 22:30:03 GMT

hi dedelman_clng, thanks for your reply. The problem is that the complete file is sensitive. I did a new table, where I filtered the IDs from the first Table, to get only those who are common in both tables. In this way the lookupvalue worked. It seems the cause were the IDs that were non-existent in the table from which I am extracting the field.

Does this help?

Re: Lookupvalue returning the complete column in blanks

dedelman_clng — Thu, 25 Feb 2021 22:36:18 GMT

HI campelliann - can you provide a screenshot of the table structure (from the model screen, not the data) and the code you are using with LOOKUPVALUE ? It may be a simple fix that doesn't require creation of a 3rd table to work.

Re: Lookupvalue returning the complete column in blanks

campelliann — Fri, 26 Feb 2021 02:04:33 GMT

I want the field "orçamento" from fields. So Lookupvalue(Fields orçamento [orçamento], Fields orçamento [ID], Beebole completo [Project ID]). It seems that the IDs that live on Beebole but not on Fields orçamento are messing this. dedelman_clng

Re: Lookupvalue returning the complete column in blanks

dedelman_clng — Fri, 26 Feb 2021 14:33:56 GMT

Hi campelliann -

You said initially that you were using 0 as an alternate result, but your code

Lookupvalue(Fields orçamento [orçamento], Fields orçamento [ID], Beebole completo [Project ID])

does not have that alternate result.

Aside from that, given that I can't see the rest of your model, it is possible that there are other filters in play that you are not aware of on either or both of the tables. You could try this:

CALCULATE( Lookupvalue(Fields orçamento [orçamento], Fields orçamento [ID], Beebole completo [Project ID], 0), ALL(Fields orcamento) )

Beyond that, without the context of seeing the entire report, I don't think there is much else I can help with.

David