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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cromo
New Member

string in values

Hi, 

I am trying to make a report where I have a Subject a score and the date of the test, 

 

So, I have my table

Subject_test:

Studentid SubjectID Subject Score Dateoftest

 

what I want to do is something similar to Pivot but i cant really add date as value what I want as a final imput is someting like this

                               SUBJECT 1

Student Id  || Subject 1 Score || Subject 1 Dateoftest ||....Subject N Score || Subject N Date of test||

 

I am able to add the score column since there are numbers but i cant add date column because it will count it.

 

 

can you help me with this?

 

thanks in advanced.

 

2 REPLIES 2
v-micsh-msft
Employee
Employee

Hi cromo,

 

According to your description, you are trying to use Matrix to show the data, right? Using Matrix in the value well will only display the numerical information. For the other column type such as text or Date/time, it will show the count numbers.

However, we could deal this with Measures, to show the date value in Matrix.

First, create a measure with the following formula:

SDATE = SUM(Sheet1[Date of test]) (Actually, we could also take use of MIN or MAX function here)

Then, when customize the Matrix Visual, put the [SDATE] measure instead of the date column, this would allow the Matrix to show the date correctly. See my testing results:

2.PNG

1.PNG

If any further questions, please feel free to post back.

Regards

 

lalthan
Resolver II
Resolver II

If you already have Student Id  || Subject 1 Score etc (assuming they are unique), you can derive the date as value by using the formula:

firstnonblank(Student Id ,1)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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