cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
keondopark Frequent Visitor
Frequent Visitor

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

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,

 

Key RowID ColID Data

Aa   1         1         100

Aa   1         2         200

Aa   2         1         300

Aa   2         2         400

Aa   3         1         500

Aa   3         2         600

Bb  1         1         A

Bb  1         2         B

Bb  2         1         C

Bb  2         2         D

Bb  3         1         E

Bb  3         2         F

 

I want to make a pivot table looking like this :

 

FIlter : Choose Aa or Bb from Key

Suppose Aa is selected,

RowID    ColID

               1       2  

1           100   200   

2           300   400

3           500   600

 

Suppose Bb is selected

RowID    ColID

               1       2  

1             A      B   

2             C      D

3             E      F

 

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?

5 REPLIES 5
Super User
Super User

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

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


greggyb New Contributor
New Contributor

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

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.

keondopark Frequent Visitor
Frequent Visitor

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

Maybe I  made a mistake in writing. 

I used Matrix for the numeric data, and Table for string data.

As you said, it automatically counts.

keondopark Frequent Visitor
Frequent Visitor

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

Can you explain in detail??

Highlighted
greggyb New Contributor
New Contributor

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

Make a measure, and instead of evaluating arithmetic, evaluate a string:

 

// 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!"
	)
)

Capture.JPG

 

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.

 

I'm happy to help more, but I don't know what sort of detail you're looking for.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 179 members 2,536 guests
Please welcome our newest community members: