Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
madawak
Frequent Visitor

Display VARCHAR values in a Matrix Table

Hi,

 

I have a table with two columns as below;

 

Assessment   Result

Test 1              84

Test 2              74

Test 3              A

 

 

I'm expecting to display these results in a Matrix table in Power BI as below;

 

 

Test 1     Test 2    Test 3

  84           74         A

 

I can get the numeric values to work in the Matrix table by creating a measure with SUM() and using this new measure as a value in the Matrix table. However, I'm unable to get results like 'A' to show in the table.

Any ideas please. 

 

 

Thanks.

4 REPLIES 4
Greg_Deckler
Super User
Super User

Try this measure:

 

Measure 8 = MAX([Result]) & "" 

I have a feeling that this won't actually work for your full use case, but it works for the data you provided. If it doesn't work for your full use case, provide more data that more accurately describers your use case.



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hi Greg,

 

Thanks for your message.

I tried to create the suggested measure and it didn't work as you expected in my use case.

 

The scenario is as below;

 

I have a table which shows students' subjects and their marks/grades for different assessments as below (I have a list of students in a Slicer).

 

Table 1.PNG

 

However, I'm trying to display this dataset in a Matrix table as below;

 

Table 2.PNG

I've created a tabular model in SSAS and that is where I have all my measures.

Matrix table requires an aggreated field to display the value (Result in this instance) and I'm unable to create an aggregated result measure as both Term 1 A&E Grade and Term 1 CIE Eqv Gr have non-numeric values as the SchoolAssessmentResultText.

 

Hope this makes sense and please let me know if you need further details.

 

Thanks.

 

Regards,

Madawa

 

Hi,

 

Create a Pivot in the Query Editor.  In the Query Editor, click on the second column's heading and click on Pivot column.  In the Aggregation drop down, select "Dont aggregate"

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

 

Thanks for your message.

 

I'm using a SSAS tabular model to bring the data into Power BI Desktop and doesn't seem to give me Pivot column option in this case.

  

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.