topic Re: Lookup Value in DAX Commands and Tips

Lookup Value

Anonymous — Wed, 26 Jan 2022 14:26:03 GMT

Hi All,

I am using a LOOKUP Function.

This is the DAX formula I am using.

EDU = LOOKUPVALUE('Workbench Export 20220126180530'[Effective Daily Usage],'Workbench Export 20220126180530'[Part Number],'Part Analysis'[PartNumber])

I am getting an error that says 'A table of multiple values was supplied where a single value was expected'

Can I please know how to correct the above formula?

Any help is appreciated!

Thank you!

Megha

Re: Lookup Value

Greg_Deckler — Wed, 26 Jan 2022 14:33:22 GMT

Anonymous If that is a measure, then you will need an aggregation around your last parameter, like MAX, MIN, etc. Also, I have found using something like MAXX(FILTER(...),...) tends to be more reliable than LOOKUPVALUE.

Re: Lookup Value

Anonymous — Wed, 26 Jan 2022 14:43:33 GMT

Thank you! Greg_Deckler

But it is neither a measure nor a calculated column.

Thank you!

Megha

Re: Lookup Value

Greg_Deckler — Wed, 26 Jan 2022 14:46:38 GMT

Anonymous OK, if it isn't a measure or a calculated column then it is a table and it is the same answer for a table as with a measure. Perhaps you could explain what you are trying to accomplish with sample data and this will go faster.

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Lookup Value

Anonymous — Wed, 26 Jan 2022 15:14:45 GMT

Greg_Deckler , I have 2 tables and I have Effective Daily Usage in Table2.

Table-1

PartNumber

35275913X

35275894X

35087611

13925560

35264448X

35087603X

Table 2

Part Number	Effective Daily Usage
13815957X	1105.73214
9402709	128.55357
12092075	220.26786
16243058	4140.39286
13862570X	26.78571
15514195X	138.44643

I am trying to Lookup Effective Daily Usage from the table 2 to table 1 for further calculation in that table.

Thank you!

Megha

Re: Lookup Value

Greg_Deckler — Wed, 26 Jan 2022 15:38:27 GMT

Anonymous Well, the formulas would be:

Column in Table-1 = MAXX(FILTER('Table 2','Table 2'[Part Number] = 'Table-1'[PartNumber]),[Effective Daily Usage]) Column in Table-1 Lookup = LOOKUPVALUE('Table 2'[Effective Daily Usage],'Table 2'[Part Number], 'Table-1'[PartNumber]) Measure = MAXX(FILTER('Table 2','Table 2'[Part Number] = MAX('Table-1'[PartNumber])),[Effective Daily Usage]) Measure Lookup = LOOKUPVALUE('Table 2'[Effective Daily Usage],'Table 2'[Part Number], MAX('Table-1'[PartNumber]))

Re: Lookup Value

Anonymous — Wed, 26 Jan 2022 16:54:22 GMT

Thank you so much! Greg_Deckler