Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |