Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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 |
---|---|
101 | |
90 | |
80 | |
71 | |
69 |
User | Count |
---|---|
114 | |
97 | |
97 | |
73 | |
72 |