DAX Measure using Lookupvalue in Excel Pivot Table

%3CLINGO-SUB%20id%3D%22lingo-sub-1494987%22%20slang%3D%22en-US%22%3EDAX%20Measure%20using%20Lookupvalue%20in%20Excel%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1494987%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20create%20a%20measure%20in%20Excel%20Power%20Pivot%20that%20will%20return%20the%20Total%20value%20for%20filtered%20rows%20in%20based%20on%20whether%20the%20column%20matches%20a%20value%20in%20another%20table.%26nbsp%3BThe%20formula%20I%20have%20is%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMX('Table1'%2CLOOKUPVALUE(%5B%24%20Allocation%5D%2CTable1%5BCost%20Centre%20Owing%5D%2CMAX('Table2'%5BCost%20Centre%5D)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20measure%20is%20syntactically%20correct%20however%20when%20I%20add%20the%20measure%20to%20my%20pivot%20table%20I%20get%20the%20message%3A%3C%2FP%3E%3CP%3EThe%20query%20did%20not%20run%20or%20the%20data%20model%20could%20not%20be%20accessed.%20MdxScript(Model)%20(6%2C%2057)%20Calculation%20error%20in%20a%20measure%20'Table1'%5BOwed%5D%20A%20table%20of%20multiple%20values%20was%20supplied%20where%20a%20single%20value%20was%20expected%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20please%20explain%20why%20this%20is%20occurring%20as%20I've%20wrapped%20the%20MAX%20function%20around%20the%20search%20value%20to%20make%20sure%20it's%20a%20single%20value.%20Thank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1494987%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
New Contributor

Hi All,

 

I'm trying to create a measure in Excel Power Pivot that will return the Total value for filtered rows in based on whether the column matches a value in another table. The formula I have is below.

 

=SUMX('Table1',LOOKUPVALUE([$ Allocation],Table1[Cost Centre Owing],MAX('Table2'[Cost Centre])))

 

This measure is syntactically correct however when I add the measure to my pivot table I get the message:

The query did not run or the data model could not be accessed. MdxScript(Model) (6, 57) Calculation error in a measure 'Table1'[Owed] A table of multiple values was supplied where a single value was expected

 

Can you please explain why this is occurring as I've wrapped the MAX function around the search value to make sure it's a single value. Thank you!

 

0 Replies