Get latest value from other table that contains blank value

[abdmajidnor]

Hi, I need to return the latest value based on date of the entry.

Table "Scoring Entry" consist of blank values and each test was submitted on different date and for different students. How am i supposed to get a table that looks like Table "Latest Scoring" that return only the latest scores for each students? 

I tried using FILTER, SUMMARIZE and LASTNONBLANKVALUE functions but that only return the result of MAX/MIN value of the scoring. 

Much Appreciated 🙂

[smpa01 · Accepted solution]

@abdmajidnor  you can achieve this with measures like this

_test1 = 
CALCULATE (
    MAX ( tbl[Test1] ),
    FILTER (
        VALUES ( tbl[Date Updated] ),
        VAR _date =
            CALCULATE (
                MAX ( tbl[Date Updated] ),
                FILTER (
                    tbl,
                    tbl[Date Updated] = LASTNONBLANKVALUE ( tbl[Test1], tbl[Date Updated] )
                )
            )
        RETURN
            tbl[Date Updated] = _date
    )
)

_test2 = 
CALCULATE (
    MAX ( tbl[Test2] ),
    FILTER (
        VALUES ( tbl[Date Updated] ),
        VAR _date =
            CALCULATE (
                MAX ( tbl[Date Updated] ),
                FILTER (
                    tbl,
                    tbl[Date Updated] = LASTNONBLANKVALUE ( tbl[Test2], tbl[Date Updated] )
                )
            )
        RETURN
            tbl[Date Updated] = _date
    )
)

_test3 = 
CALCULATE (
    MAX ( tbl[Test3] ),
    FILTER (
        VALUES ( tbl[Date Updated] ),
        VAR _date =
            CALCULATE (
                MAX ( tbl[Date Updated] ),
                FILTER (
                    tbl,
                    tbl[Date Updated] = LASTNONBLANKVALUE ( tbl[Test3], tbl[Date Updated] )
                )
            )
        RETURN
            tbl[Date Updated] = _date
    )
)

[AlexisOlson]

I think it makes more sense to define your date variable outside of the first CALCULATE. Doubly nested CALCULATE(..., FILTER(...)) shouldn't be necessary here.

For example,

_test1 =
VAR _date =
    CALCULATE ( MAX ( tbl[Date Updated] ), NOT ISBLANK ( tbl[Test1] ) )
RETURN
    CALCULATE ( MAX ( tbl[Test1] ), tbl[Date Updated] = _date )

Or for those who don't like CALCULATE,

_test1 =
MAXX (
    TOPN (
        1,
        FILTER ( tbl, NOT ISBLANK ( tbl[Test1] ) ),
        tbl[Date Updated]
    ),
    tbl[Test1]
)

[CNENFRNL]

In practice, you should unpivot the dataset if you want to deal with 7, 8 .. tests instead of 3.

Another tricky solution in PQ,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMjIAEkDkmFsJJA31DfWNDIwMlWJ1opVMoVLYZSESxiZAwis/Iw+LtLEpRA1c2ghF2gzNZiN0s01BZjtnZBZlFmNXYGyEbLopQtoQajTYBzDzTbH6DK7dDNXtIBUmBqgOQFJiZo5LkTlUUSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Test1 = _t, Test2 = _t, Test3 = _t, Student = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Test1", Int64.Type}, {"Test2", Int64.Type}, {"Test3", Int64.Type}, {"Student", type text}, {"Date", type date}}),
    #"Lastest Score" = Table.Group(#"Changed Type", "Student", {"ar", each Table.Last(Table.FillDown(_, {"Test1", "Test2", "Test3"}))}),
    #"Expanded ar" = Table.ExpandRecordColumn(#"Lastest Score", "ar", {"Test1", "Test2", "Test3"}, {"Test1 ", "Test2", "Test3"})
in
    #"Expanded ar"

[ValtteriN]

Hi,

This should do what you are looking for:
Test data:

Dax:

Summarized scores = SUMMARIZE(TestScores,TestScores[Student],

"Test1", LASTNONBLANKVALUE(TestScores[Test1],summarize(TestScores,TestScores[Test1])),

"Test2", LASTNONBLANKVALUE(TestScores[Test2],summarize(TestScores,TestScores[Test2])),

"Test3", LASTNONBLANKVALUE(TestScores[Test3],summarize(TestScores,TestScores[Test3]))

)

End result:

I hope this helps and if it does consider accpeting this as a solution!