Summarise student results in a table or matrix

[Elisa112]

Hi Experts

I have a student assessment table as below:

Student ID      Assess Date   Assess Type      Assess Stage       Assess Result     

  124                  1 Jan                  ESS               First Stage            Level 1            

  124                  15 Jan                INT               First Stage            Level 2             

  124                  1 Feb                  ESS              First Review          Level 3            

  124                  1 Mar                 ESS               Second Review    Level 3            

  124                  30 Jan                INT              First Review          Level 2             

  124                  30 March           ESS               Third Review        Level 3             

I am having difficulty creating a table or matrix visual to show assessment results for ESS only, the expected result is below:

Expected Result is a table or matrix, 1 row per student, with date and results for each stage

ID   1st Stage Date    1st Stage Result    1st Review Date   1st Review Result

124   1 Jan                     Level 1                    1 Feb                  Level 3

I have tried to summrise the main table however I get dax code errors.

Thanks in advance, all help appreciated 

[Anonymous · Accepted solution]

Hi @Elisa112 ,
Thanks for lbendlin, Ashish_Mathur and Kedar_Pande reply.

Here is another way to achieve this
Sample Data

Student ID	Assess Date	Assess Type	Assess Stage	Assess Result
124	1-Jan	ESS	First Stage	Level 1
124	15-Jan	INT	First Stage	Level 2
124	1-Feb	ESS	First Review	Level 3
124	1-Mar	ESS	Second Review	Level 3
124	30-Jan	INT	First Review	Level 2
124	30-Mar	ESS	Third Review	Level 3
125	2-Jan	ESS	First Stage	Level 1
125	16-Jan	INT	First Stage	Level 2
125	2-Feb	ESS	First Review	Level 3
125	2-Mar	ESS	Second Review	Level 3
125	31-Jan	INT	First Review	Level 2
125	31-Mar	ESS	Third Review	Level 3

1.Open Power Query and edit code in advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyUdJRMtT1SswD0q7BwUDSLbOouEQhuCQxPRXI80ktS81RMFSK1YGrNoUq9/QLwaHcCFm5rltqEprhQallmanlcOXGKMp9E4vgyoNTk/PzUqDqDy3AqsPYAIt70GwwQlWPbEVIRmZRCi4XmQJFjIgOHZBqQzOiQwdiONGhA1FOSuiAdBgbEh86UPXEhE4sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student ID" = _t, #"Assess Date" = _t, #"Assess Type" = _t, #"Assess Stage" = _t, #"Assess Result" = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Student ID", "Assess Type", "Assess Stage"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Assess Type] = "ESS") and ([Assess Stage] = "First Review" or [Assess Stage] = "First Stage")),
    AddColumn = Table.AddColumn(#"Filtered Rows", "Column", each if [Assess Stage] = "First Stage" and [Attribute] = "Assess Date" then "1st Stage Date" 
                                else if [Assess Stage] = "First Stage" and [Attribute] = "Assess Result" then "1st Stage Result" 
                                else if [Assess Stage] = "First Review" and [Attribute] = "Assess Date" then "1st Review Date" 
                                else if [Assess Stage] = "First Review" and [Attribute] = "Assess Result" then "1st Review Result" 
                                else null)
in
    AddColumn

2.Close and apply and create matrix

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Kedar_Pande]

@Elisa112 

Calculated Table:

ESS_Assessments = 
FILTER(
'AssessmentTable',
'AssessmentTable'[Assess Type] = "ESS"
)

1st Stage Date = 
CALCULATE(
MIN('ESS_Assessments'[Assess Date]),
'ESS_Assessments'[Assess Stage] = "First Stage"
)

1st Stage Result = 
CALCULATE(
FIRSTNONBLANK('ESS_Assessments'[Assess Result], 1),
'ESS_Assessments'[Assess Stage] = "First Stage"
)

1st Review Date = 
CALCULATE(
MIN('ESS_Assessments'[Assess Date]),
'ESS_Assessments'[Assess Stage] = "First Review"
)

1st Review Result = 
CALCULATE(
FIRSTNONBLANK('ESS_Assessments'[Assess Result], 1),
'ESS_Assessments'[Assess Stage] = "First Review"
)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Ashish_Mathur]

Hi,

Would you be OK with a Power Query solution?  Also, in the Assess Date column, you do not have the year mentioned.  Does that mean that those are text entries?

[Elisa112]

Yes any solution would be great, I have just used example data in the question so all dates are of type date in the actual table

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student ID", type text}, {"Assess Date", type date}, {"Assess Type", type text}, {"Assess Stage", type text}, {"Assess Result", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Assess Type] = "ESS")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Assess Type"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Student ID", "Assess Stage"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each [Assess Stage]&" "&Text.AfterDelimiter([Attribute]," ")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Assess Stage", "Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Custom]), "Custom", "Value")
in
    #"Pivoted Column"

Hope this helps.

[lbendlin]

Needs a little help - a Sort column that indicates how the column headers should be sorted

[Elisa112]

Hi there did you mean how should the columns be ordered?, If so Column headers should be in the forllowing order:

1.Student ID

2.1st stage date

3.1st stage result

4.1st Review Date

5.1st Review Result

5.2nd Review Date

6.2nd Review Result

7.3rd Review Date

8.3rd Review Result

Thanks for your assistance