Home

LOOKUPVALUE DAX function, in excel pivot table

%3CLINGO-SUB%20id%3D%22lingo-sub-282931%22%20slang%3D%22en-US%22%3ELOOKUPVALUE%20DAX%20function%2C%20in%20excel%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-282931%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20pivot%20table%20with%20a%20row%20field%20(%22name%22)%20that%20has%20the%20names%20of%20some%20elements%20of%20the%20periodic%20table.%26nbsp%3B%20There%20are%20three%20value%20fields%20-%20the%20first%20a%20count%20of%20the%20occurence%20of%20another%20field%20%22sap_batch%22%20and%20the%20other%20two%20the%20average%2Fstd%20dev%20of%20a%20%22RESULT%22%20field.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20another%20table%20(%22CLTBL1%22)%2C%20a%20list%20object%2C%20on%20the%20page%20that%20has%20several%20fields.%26nbsp%3B%20One%20of%20them%20is%20called%20%22analyte%22%20and%20another%20called%20%22CLASS%22.%26nbsp%3B%20The%20%22analyte%22%20field%20in%20CLTBL1%20is%20identical%20in%20terms%20of%20contents%20to%20the%20%22name%22%20field%20in%20the%20pivot%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20the%20LOOKUPVALUE%20function%20to%20get%20the%20contents%20of%20the%20%22CLASS%22%20field%20from%20CLTBL1%20into%20the%20pivot%20table%20based%20on%20the%20value%20in%20the%20%22name%22%20field.%26nbsp%3B%20Here%20is%20the%20syntax%20I'm%20using%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DLOOKUPVALUE(CLTBL1%5BCLASS%5D%2C%20CLTBL1%5BAnalyte%5D%2C%20name)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAm%20I%20missing%20something%3F%26nbsp%3B%20For%20whatever%20reason%2C%20when%20I%20click%20the%20add%20button%20in%20the%20Insert%20Calculated%20Field%20window%2C%20the%20window%20simply%20highlights%20the%20portion%20of%20the%20formula%20that%20reads%20%22CLTBL1%5BCLASS%5D%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20guess%20is%20there's%20a%20formatting%20issue%2C%20but%20I've%20followed%20every%20guide%20I%20could%20locate%20on%20the%20matter%20and%20I%20don't%20see%20issue%20with%20formatting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20help%20is%20much%20appreciated%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20B%20Latif%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20319px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F59159i8D4C0398A5AD824A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20606px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F59161iF1D7D7C97953AD09%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-282931%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ERequests%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-285874%22%20slang%3D%22en-US%22%3ERe%3A%20LOOKUPVALUE%20DAX%20function%2C%20in%20excel%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-285874%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F238676%22%20target%3D%22_blank%22%3E%40B%20Latif%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIs%20your%20Pivot%20Table%20created%20with%20Power%20Pivot%3F%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20only%20use%20DAX%20when%20using%20a%20Pivot%20Table%20created%20using%20Power%20Pivot.%3C%2FP%3E%3C%2FLINGO-BODY%3E
B Latif
Occasional Visitor

Hello everyone,

 

I have a pivot table with a row field ("name") that has the names of some elements of the periodic table.  There are three value fields - the first a count of the occurence of another field "sap_batch" and the other two the average/std dev of a "RESULT" field.

 

I have another table ("CLTBL1"), a list object, on the page that has several fields.  One of them is called "analyte" and another called "CLASS".  The "analyte" field in CLTBL1 is identical in terms of contents to the "name" field in the pivot table.

 

I am trying to use the LOOKUPVALUE function to get the contents of the "CLASS" field from CLTBL1 into the pivot table based on the value in the "name" field.  Here is the syntax I'm using:

 

=LOOKUPVALUE(CLTBL1[CLASS], CLTBL1[Analyte], name)

 

Am I missing something?  For whatever reason, when I click the add button in the Insert Calculated Field window, the window simply highlights the portion of the formula that reads "CLTBL1[CLASS]".

 

My guess is there's a formatting issue, but I've followed every guide I could locate on the matter and I don't see issue with formatting.

 

Your help is much appreciated,

 

- B Latif  

 

image.pngimage.png

1 Reply

Hi @B Latif

 

Is your Pivot Table created with Power Pivot?   

 

You can only use DAX when using a Pivot Table created using Power Pivot.

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies