Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 🙂
Solved! Go to 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
)
)
@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
)
)
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]
)
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"
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! |
Hi,
This should do what you are looking for:
Test data:
Dax:
End result:
I hope this helps and if it does consider accpeting this as a solution!
Proud to be a Super User!
User | Count |
---|---|
40 | |
26 | |
22 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |