topic LOOKUPVALUE not reporting all availabe data in DAX Commands and Tips

LOOKUPVALUE not reporting all availabe data

CJC_DB — Thu, 24 Nov 2022 13:06:01 GMT

Dear all,

i have 2 tables whre i need to combine the data set. I tried to achive this by RELATED or LOOKUPVALUE but both approaches report only a part of the available data.

picture I: report table which contains the LOOKUPVALUE fxt

SN_OEM = LOOKUPVALUE(BOM[Merged], BOM[IAL Part Number], ANF[P/N])

the second table is containing the necessary data. As you may see the secontable is containig the required information but it is not reporting it back. Both tables are sorted by p/n

picture2: table for lookupvalues

Does somebody have an i dear what i need to change in order to reiceive the complete data set.

Thank you and best regards!

Re: LOOKUPVALUE not reporting all availabe data

Greg_Deckler — Thu, 24 Nov 2022 13:51:20 GMT

CJC_DB Try this:

SN_OEM = MAXX(FILTER('BOM',[IAL Part Number] = 'ANF'[P/N]),[Merged])

If that doesn't work then try doing a Trim operation in Power Query for P/N and IAL Part Number columns.

Re: LOOKUPVALUE not reporting all availabe data

SamVS — Thu, 24 Nov 2022 13:58:33 GMT

I wonder if there's perhaps some white space (like a space at the end) in either BOM[IAL Part Number] or ANF[P/N].

Just curious if you add a column with a formula like this, what happens?

LookupValueTest = CALCULATE(MAX(BOM[Merged]), FILTER(ALL(BOM),BOM[IAL Part Number] = ANF[P/N]))

Re: LOOKUPVALUE not reporting all availabe data

NikhilChenna — Thu, 24 Nov 2022 14:25:13 GMT

Hi CJC_DB , I think i found a solution.

1. Go to Power Query Editor and select this [P/N] column in the first table and right click and go to transform - > text transforms -> trim as show in the below table. And do the same for the second table [P/N] column.

2. Click close and apply.

3. Then try the lookup again it should work. I tried my self with one example and it works.

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

Re: LOOKUPVALUE not reporting all availabe data

CJC_DB — Thu, 24 Nov 2022 15:13:17 GMT

Greg_Deckler SamVS NikhilChenna , thank you very much for your support! i followed first the solution proposed by Greg_Deckler . Afterwards all values have been displayed.

Thanks alot!