Showing results for 
Search instead for 
Did you mean: 
Regular 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?

Super User
Super User

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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

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.

Can you explain in detail??

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! = 
	VALUES( 'Sample'[SomeField] )
		,1, "That's a one"
		,2, "Well that looks like a 2"
		,3, "By golly, thats a seventeen!"



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

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors