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

Get latest value from other table that contains blank value

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. 


Score Entry.png

 

Latest Score.png

Much Appreciated 🙂

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@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
    )
)

 

 

smpa01_0-1639244283534.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

4 REPLIES 4
smpa01
Super User
Super User

@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
    )
)

 

 

smpa01_0-1639244283534.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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
Community Champion
Community Champion

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"

 

 

CNENFRNL_0-1639223112259.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

ValtteriN
Super User
Super User

Hi,

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

ValtteriN_0-1639220699151.png


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:

ValtteriN_1-1639220812068.png

 


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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

Top Solution Authors