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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Abi_W_981
Frequent Visitor

Finding First Result using Date & Unique ID

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

 

Unique IDGrade ValueResult Date
1596501/10/2022
1596620/12/2022
1596615/02/2023
1645401/10/2022
1645319/12/2022
1645413/02/2023
1723201/10/2022
1723220/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?

2 ACCEPTED SOLUTIONS
ronrsnfld
Super User
Super User

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"

 

 

ronrsnfld_0-1677154454573.png

 

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

View solution in original post

Hi,

select the column and FillDown

serpiva64_0-1677156006491.png

 

View solution in original post

7 REPLIES 7
dufoq3
Super User
Super User

Hi @Abi_W_981, another one:

 

Result:

dufoq3_0-1709410244648.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Tinus1905
Resolver I
Resolver I

@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
Frequent Visitor

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

ronrsnfld
Super User
Super User

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"

 

 

ronrsnfld_0-1677154454573.png

 

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

serpiva64
Solution Sage
Solution Sage

Hi,

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

you can obtain this

serpiva64_0-1677153358036.png

by group by

serpiva64_1-1677153387682.png

then expand

serpiva64_2-1677153410420.png

and adding a calculated column

serpiva64_3-1677153435383.png

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!

 

 

 

 

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

Hi,

select the column and FillDown

serpiva64_0-1677156006491.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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

Top Solution Authors
Top Kudoed Authors