Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have Table1 which contains 4 occurrences of the same ID happening in different date/times and remarks.
What i need to do is to return the most recent remarks which is "A". I tried using max value however it is only returning me the sorted alphabet which is "D" and i also used lookup value to return the last remark from the max date however remark "B" and "C" was updated at the same time.
ID | Date | Remarks |
ABC123 | 01/06/2021 12:31:18 | B |
ABC123 | 01/07/2021 12:31:18 | C |
ABC123 | 01/08/2021 12:31:19 | D |
ABC123 | 01/09/2021 12:31:20 | E |
ABC123 | 01/10/2021 12:31:18 | A |
Solved! Go to Solution.
Hi @ggzmorsh ,
You can create a measure as below:
Latest remark =
VAR _maxdate =
CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
RETURN
CALCULATE (
MAX ( 'Table'[Remarks] ),
FILTER (
'Table',
'Table'[ID] = MAX ( 'Table'[ID] )
&& 'Table'[Date] = _maxdate
)
)
Best Regards
Hi @ggzmorsh ,
You can create a measure as below:
Latest remark =
VAR _maxdate =
CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
RETURN
CALCULATE (
MAX ( 'Table'[Remarks] ),
FILTER (
'Table',
'Table'[ID] = MAX ( 'Table'[ID] )
&& 'Table'[Date] = _maxdate
)
)
Best Regards
This is the closest solution from what i have. However since we using the MAX in the dax that means if there is two dates with the same value it will return the sorted remarks from Z-A. This brings light! thank you.
@ggzmorsh , Try measure like
lastnonblankvalue(Table[Date],Max(Table[Remark]))
or
calculate(lastnonblankvalue(Table[Date],Max(Table[Remark])), allexpect(Table, Table[ID]))
My dear, I have used the formula
Last Remark = LASTNONBLANKVALUE(Table1[Date],MAX(Table1[Remark]))
I have tried your solution and it worked at the topic at hand. I will mark it as an accepted solution once i test it to a larger data and verify the results.
How about like this? Pull in a visual filter for Date and use Top 1.
I needed it as a dax column.
User | Count |
---|---|
77 | |
74 | |
63 | |
61 | |
45 |
User | Count |
---|---|
108 | |
102 | |
93 | |
83 | |
64 |