Home

Power Pivot & Data Model: showing text in value field

%3CLINGO-SUB%20id%3D%22lingo-sub-808680%22%20slang%3D%22en-US%22%3EPower%20Pivot%20%26amp%3B%20Data%20Model%3A%20showing%20text%20in%20value%20field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-808680%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Everyone!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20help%20with%20power%20pivot%20and%20data%20model.%20I'm%20in%20the%20process%20of%20building%20a%20LMS%20for%20my%20department.%20I'm%20creating%20a%20pivot%20table%20to%20help%20the%20IT%20guys%20input%20the%20curriculum%20and%20the%20training%20matrix%20into%20the%20LMS.%20However%2C%20I%20have%20been%20unable%20to%20make%20power%20model%20and%20pivot%20to%20display%20text%20in%20value%20field.%20For%20context%2C%20all%20of%20my%20data%20is%20text%20based%20(e.g.%20course%20titles%2C%20course%20code%20such%20as%20XYX-1234%2C%20titles%20of%20positions%2C%20job%20prescriptions%2C%20etc.)%20and%20there%20are%20no%20numerical%20values%20at%20all.%20I%20tried%20setting%20up%20measures%20to%20so%20pivot%20table%20can%20read%20text%2C%20but%20It%20only%20works%20for%20couple%20of%20columns%20then%2C%20whatever%20additional%20measures%20I%20add%20are%20deemed%20as%20invalid%20by%20excel.%20How%20can%20I%20fix%20this%3F%20Thank%20you!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-808680%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-810109%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Pivot%20%26amp%3B%20Data%20Model%3A%20showing%20text%20in%20value%20field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-810109%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F393635%22%20target%3D%22_blank%22%3E%40Zsayna%3C%2FA%3E%2C%26nbsp%3B%20pivot%20tables%20cannot%20show%20text%20in%20the%20value%20area.%20The%20value%20area%20is%20strictly%20for%20numbers.%20Pivot%20tables%20aggregate%20numbers%20in%20the%20value%20area%20to%20show%20sum%2C%20count%2C%20average%2C%20etc.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20could%20post%20a%20small%20data%20sample%20in%20an%20Excel%20file%20and%20manually%20mock%20up%20the%20desired%20result%2C%20we%20may%20be%20able%20to%20find%20a%20way%20to%20achieve%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-810589%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Pivot%20%26amp%3B%20Data%20Model%3A%20showing%20text%20in%20value%20field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-810589%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F393635%22%20target%3D%22_blank%22%3E%40Zsayna%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3ETry%20the%20DAX%20ConcatenateX%20after%20adding%20your%20data%20to%20the%20data%20model.%20Right%20click%20on%20the%20item%20you%20wanna%20add%20and%20choose%20''add%20to%20measure''.%20Please%20see%20link%20below%20for%20further%20guidance.%20Hope%20that%20helps.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fforums%2Freplypage%2Fboard-id%2FExcelGeneral%2Fmessage-id%2F38403%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fforums%2Freplypage%2Fboard-id%2FExcelGeneral%2Fmessage-id%2F38403%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Zsayna
Occasional Visitor

Hi Everyone! 

 

I need help with power pivot and data model. I'm in the process of building a LMS for my department. I'm creating a pivot table to help the IT guys input the curriculum and the training matrix into the LMS. However, I have been unable to make power model and pivot to display text in value field. For context, all of my data is text based (e.g. course titles, course code such as XYX-1234, titles of positions, job prescriptions, etc.) and there are no numerical values at all. I tried setting up measures to so pivot table can read text, but It only works for couple of columns then, whatever additional measures I add are deemed as invalid by excel. How can I fix this? Thank you! 

 

2 Replies

Hello @Zsayna,  pivot tables cannot show text in the value area. The value area is strictly for numbers. Pivot tables aggregate numbers in the value area to show sum, count, average, etc.

 

If you could post a small data sample in an Excel file and manually mock up the desired result, we may be able to find a way to achieve that.

@Zsayna 

 

Hi

Try the DAX ConcatenateX after adding your data to the data model. Right click on the item you wanna add and choose ''add to measure''. Please see link below for further guidance. Hope that helps. 

https://techcommunity.microsoft.com/t5/forums/replypage/board-id/ExcelGeneral/message-id/38403