Finding First Result using Date & Unique ID

[Abi_W_981]

Hi, I am trying to find the first Grade value using Result Date and a Unique ID (Example table below)

Unique ID	Grade Value	Result Date
1596	5	01/10/2022
1596	6	20/12/2022
1596	6	15/02/2023
1645	4	01/10/2022
1645	3	19/12/2022
1645	4	13/02/2023
1723	2	01/10/2022
1723	2	20/12/2022

My aim is to create an additional column finding out for each unique ID what the first grade value is using both the Result Date and Unique ID.

I am in Power BI Query - is anyone able to help with a custom column calculation formula?

[ronrsnfld · Accepted solution]

If your data is guaranteed to be sorted by Unique ID and Date (Ascending) as you show in your example, then:

• Group by Unique ID
• Extract the first Grade Value
• Re-expand the table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY7BDcAgDAN3yRspiUOomAWx/xptilSKlIdfZ588Bqn3RoX8iSirMASgWT4SgbAiJeosL7FFWg1TTWyLWGz6adsbtdN2IfpIbJv8v80b", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, #"Grade Value" = _t, #"Result Date" = _t]),
    
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Unique ID", Int64.Type}, {"Grade Value", Int64.Type}, {"Result Date", type date}}, "en-GB"),

//Add index value to force Table.Group to maintain sort order
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    
    #"Grouped Rows" = Table.Group(#"Added Index", {"Unique ID"}, {
        {"all", each _, type table [Unique ID=nullable number, Grade Value=nullable number, Result Date=nullable date, Index=number]},
        {"First Grade Value", each [Grade Value]{0}, Int64.Type}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Grade Value", "Result Date"})
in
    #"Expanded all"

If your data is NOT sorted, then you will need to add a sort step.

[dufoq3]

Hi @Abi_W_981, another one:

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY7BDcAgDAN3yRspiUOomAWx/xptilSKlIdfZ588Bqn3RoX8iSirMASgWT4SgbAiJeosL7FFWg1TTWyLWGz6adsbtdN2IfpIbJv8v80b", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, #"Grade Value" = _t, #"Result Date" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Result Date", type date}}, "sk-SK"),
    GroupedRows = Table.Group(ChangedType, {"Unique ID"}, {{"All", each _}, {"Min Grade", each Table.Min(_, "Result Date")[Grade Value], type table}}),
    Ad_MinGrade = Table.AddColumn(GroupedRows, "t", each Table.AddColumn([All], "Min Grade", (x)=> Number.From([Min Grade]), Int64.Type)),
    CombinedT = Table.Combine(Ad_MinGrade[t])
in
    CombinedT

[Tinus1905]

@serpiva64 I have a simular case: https://community.fabric.microsoft.com/t5/Desktop/First-value-to-find-after-column-value/m-p/3717008 

The solution you gave in this toppic is exactly what I need. But when I try this with my table its goes wrong. 

Can you help me with this?

[Abi_W_981]

Thank you @ronrsnfld this solution seems to have worked for me - appreciate it!

[ronrsnfld · Accepted solution]

If your data is guaranteed to be sorted by Unique ID and Date (Ascending) as you show in your example, then:

• Group by Unique ID
• Extract the first Grade Value
• Re-expand the table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY7BDcAgDAN3yRspiUOomAWx/xptilSKlIdfZ588Bqn3RoX8iSirMASgWT4SgbAiJeosL7FFWg1TTWyLWGz6adsbtdN2IfpIbJv8v80b", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, #"Grade Value" = _t, #"Result Date" = _t]),
    
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Unique ID", Int64.Type}, {"Grade Value", Int64.Type}, {"Result Date", type date}}, "en-GB"),

//Add index value to force Table.Group to maintain sort order
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    
    #"Grouped Rows" = Table.Group(#"Added Index", {"Unique ID"}, {
        {"all", each _, type table [Unique ID=nullable number, Grade Value=nullable number, Result Date=nullable date, Index=number]},
        {"First Grade Value", each [Grade Value]{0}, Int64.Type}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Grade Value", "Result Date"})
in
    #"Expanded all"

If your data is NOT sorted, then you will need to add a sort step.

[serpiva64]

Hi,

I don't know if i have really understood your problem

you can obtain this

by group by

then expand

and adding a calculated column

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!

[Abi_W_981]

Hi, thank you for your solutions so far

That could work however, I would need the First Result to show for all of the rows for that Unique ID.

The reason being is that I will then add another measure/column to calculate the difference between the first grade value to the subsequent grade values to identify what progress has been made

[serpiva64 · Accepted solution]

Hi,

select the column and FillDown