# How to show text into a pivot table



## michelv

Hi there,

I'm a bit new to this and hopefully you can give me an answer. I'm trying to put survey results into a power pivot table and for the questions that relates to an answer it all goes perfect. But...if people have the choice to fill in text, how can I show this in a nice way in Power pivot?

One other question, can I limit the vertical slicer. For instance there are 20 question (1 til 20) and I only want to be able to choose from 10 till 14 to show in the slice, is this possible (without excluding it in the database file)

Thanks in advance


----------



## powerpivotpro

To return a text value from your source tables as a measure, try the following measure formula pattern:



		Code:
__


=IF(COUNTROWS(VALUES(Table[TextColumn])) > 1, BLANK(),
   VALUES(Table[TextColumn])
 )

And your other question:  no there is no way to limit what shows up on the slicer without modifying your underlying table data, sorry.


----------



## David Churchward

Hi Michelv

Rob's given you the goods on part one of your question.  With regard to part 2, Rob is right, but there is a possibly "tenuous" way of doing it without having to hack your data depending on what you're trying to show.  I've done something similar, but not in production!

Create a linked table with the question numbers that you want to include, but don't relate it to any tables in your dataset.  Somthing like this:

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 48pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 15pt; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" height=20 width=64>Q</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20 align=right>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20 align=right>11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20 align=right>12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20 align=right>13</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20 align=right>14</TD></TR></TBODY></TABLE>

I've called this Table2 and your core table Table1.

Use the following DAX measure


=CALCULATE(
VALUES(Table1[Answer]),
FILTER(Table1,
COUNTROWS(VALUES(Table1[Answer]))=1),
FILTER(table1,
COUNTROWS(FILTER(VALUES(Table1[Question]),
EARLIER(Table1[Question])=VALUES(Table2[Q])))>0)
)

Make sure that you have a slicer for Table2[Q] connected to your pivot.  If only one slicer option is selected, it will provide results.  If more than one value is selected, it will display nothing but shouldn't give you any ugly warnings.  This slicer will only have the limited set of question numbers that you want to display.

To explain, the first filter makes sure that you only have one answer to display.  This is the same as Rob's IF statement in effect.  If you have multiple answers to the same question, your pivot will need to display a row reference that ensures that you'll get just one answer on that row.  The second filter limits results to those questions that have values in the secondary table.

<TABLE style="WIDTH: 428pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=570><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5485" width=150><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=92></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 113pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=150></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=72></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">Row Labels</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">Ans</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 198pt; HEIGHT: 90pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan-rowspan" height=120 rowSpan=6 width=264 colSpan=4><?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>12</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">First answer to question</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Second answer</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR></TBODY></TABLE>

I've got a feeling that you can get this to work with multiple question selections, but I haven't tried it.

I hope this helps and good luck 
David


----------



## David Churchward

Tweaked the measure a bit and it now works with multiple selections on the slicer:


=IF(COUNTROWS(VALUES(Table1[ID]))=1,
CALCULATE(
VALUES(Table1[Answer]),
FILTER(Table1,
COUNTROWS(FILTER(Table1,
EARLIER(Table1[Question])=VALUES(Table2[Q]))
)>0)
)
)

The only health warning is that the question number from Table2 (Table2[Q] in this case) has to feature on the pivot.

Hope this gives you what you need.


----------



## michelv

Thank you very much for your replies guys.
I'll try to put it into practice today and hopefully it works for me.
I appreciate it!


----------



## michelv

David, I'm trying my best but for some reason I can't get it to work.
Is it possible you can give me a working example, I thought perhaps you've created one for testing your formula.

Thanks for your help so far!


----------



## David Churchward

I'll message you directly and we can report back when the solution is working.

Thanks


----------



## michelv

The solution I came up with was to create Views in the database to show the answers in a slicer and to limit the questions I did the same. 

Unfortunately for some reason I can't create relations between the views and the tables. Well actually, Powerpivot let's me create them (and accepts them as well) but they don't work.


----------



## michelv

BTW, I was very grateful, David was a big help to me with (all) my questions related to Powerpivot.


----------



## michelv

michelv said:


> Unfortunately for some reason I can't create relations between the views and the tables. Well actually, Powerpivot let's me create them (and accepts them as well) but they don't work.



Everything works, I've copied a column from an existing table into another table with the exact same data. I'm not sure but is it possible that powerpivot won't except multiple relations with the same column/table?!


----------



## michelv

Hi there,

I'm a bit new to this and hopefully you can give me an answer. I'm trying to put survey results into a power pivot table and for the questions that relates to an answer it all goes perfect. But...if people have the choice to fill in text, how can I show this in a nice way in Power pivot?

One other question, can I limit the vertical slicer. For instance there are 20 question (1 til 20) and I only want to be able to choose from 10 till 14 to show in the slice, is this possible (without excluding it in the database file)

Thanks in advance


----------



## dmulle

I am trying to get your suggestion to work, but need more detail.  I am a bit rusty with my excel.  Where do you insert the formula below?  I tried inserting a calculated field, this was the only place I could think of - I replaced Table in your formula with the table name/range and the Text Column with the field name by inserting from the list in the calculated field box.  However this did not work.  if you dont put the correct formula it will not accept and do not offer any clues as to what you might need to change.

Any suggestions?  I am needing to return comments along side a value to explain the number, the vaule and the comments are seperate fields.

Thanks




powerpivotpro said:


> To return a text value from your source tables as a measure, try the following measure formula pattern:
> 
> 
> 
> Code:
> __
> 
> 
> =IF(COUNTROWS(VALUES(Table[TextColumn])) > 1, BLANK(),
> VALUES(Table[TextColumn])
> )
> 
> And your other question:  no there is no way to limit what shows up on the slicer without modifying your underlying table data, sorry.


----------



## dmulle

Here is a sample of what I am trying to pivot - top line is my column headers, detail is on second line.  I need the Explanation to appear in the pivot next to the Variance

ISLine Item20122011Variance% Chg.Explanation

<tbody>

</tbody><colgroup><col><col><col span="3"><col><col><col></colgroup>
Other Income:Exchange (gain) or loss145,594(2,536)148,130(5841.0%)This account represents currency revaluation effect

<tbody>

</tbody><colgroup><col><col><col span="3"><col><col><col></colgroup>


----------

