How can I make string values to appear on pivot table?

Question

Hi, I am now trying to make the date records into table format.
But the data column contains both numeric and string values
For example, if you have a dataset as below,
&nbsp;
Key RowID ColID Data
Aa &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; 100
Aa &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; 200
Aa &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; 300
Aa &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; 400
Aa &nbsp; 3 &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; 500
Aa &nbsp; 3 &nbsp; &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; 600
Bb &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; A
Bb &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; B
Bb&nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; C
Bb &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; D
Bb &nbsp;3 &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; E
Bb &nbsp;3 &nbsp; &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; F
&nbsp;
I want to make a pivot table looking like this :
&nbsp;
FIlter : Choose Aa or Bb from Key
Suppose Aa is selected,
RowID &nbsp; &nbsp;ColID
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 2 &nbsp;
1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100 &nbsp; 200 &nbsp;&nbsp;
2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 300 &nbsp; 400
3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;500&nbsp; &nbsp;600
&nbsp;
Suppose Bb is selected
RowID &nbsp; &nbsp;ColID
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 2 &nbsp;
1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A &nbsp; &nbsp; &nbsp;B &nbsp;&nbsp;
2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; C &nbsp; &nbsp; &nbsp;D
3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; E &nbsp; &nbsp; &nbsp;F
&nbsp;
But if i want to make a pivot table, I cannot make string values shown on the table...
The only way I have is to divide the full dataset into two different dataset by KEY, and to use Matrix on dashboard for string data.
Can I find any other way without dividng the full dataset?

greg_deckler · Answer

I'm not sure how you are doing that with a Matrix visualization honestly, if you try to use a Text field in the Value for a Matrix, it tends to want to count it versus giving you the text.

greggyb · Answer

A measure can return a text datatype. If you can write a measure that evaluates to a single text value appropriately, it should work in a matrix.

greggyb · Answer

Make a measure, and instead of evaluating arithmetic, evaluate a string:&nbsp;// DAX
// All below are measures in my model

MyMeasureReturnsText! = 
"This measure returns text"

This Measure returns text based on a field value! = 
CONCATENATEX(
	VALUES( 'Sample'[SomeField] )
	,SWITCH(
		'Sample'[SomeField]
		,1, "That's a one"
		,2, "Well that looks like a 2"
		,3, "By golly, thats a seventeen!"
	)
)&nbsp;My 'Sample' is just a 3-row table with values of [SomeField] = 1, 2, 3.You can see that my matrix uses [SomeField] as the row labels. My measures are both in the Values area. Each returns text.&nbsp;I'm happy to help more, but I don't know what sort of detail you're looking for.

keondopark · Answer

Maybe I &nbsp;made a mistake in writing.&nbsp;I used Matrix for the numeric data, and Table for string data.As you said, it automatically counts.

keondopark · Answer

Can you explain in detail??

Forum Discussion

How can I make string values to appear on pivot table?

5 Replies

Related content

Replacing string values in a query column with values from another query

Extract Alphanumeric Value from String

Count( if column value = string)

concatenate strings

Add text string to value