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
Highlighted
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
Highlighted

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.