Forum Discussion
Find Most recent Record with Score
I have a table that lists the following. What i am wanting is the most recent date and score of each Case #.
| Case# | Admission Date | Staff Member | Score |
| 1 | 1/1/2019 | John | 20 |
| 2 | 1/2/2019 | John | 35 |
| 1 | 2/1/2019 | John | 40 |
| 2 | 2/2/2019 | John | 20 |
So i would want the following result from dax.
| 1 | 2/1/2019 | John | 40 |
| 2 | 2/2/2019 | John | 20 |
I was trying to use the max filter but was not having any luck. I would just do a latest filter from the visual but i need to add this score to another measure for a total score. I figured if i could first find out the most recent score that was given i could then add it to the Total measure that i have. Here is what i was trying to find most recent score.
CALCULATE(MAX(Admissions[Admission Date]))
But it was just giving me the same date with scores in my visual instead of the singluar Distinct records i need. Any help would be greatly appreciated!
10 Replies
Try
Measure = VAR __id = MAX ( 'Table'[Case#] ) VAR __date = CALCULATE ( MAX( 'Table'[Admission Date] ), ALLSELECTED ( 'Table' ), 'Table'[Case#] = __id ) RETURN CALCULATE ( sum ( 'Table'[Score] ), VALUES ( 'Table'[Case#] ), 'Table'[id] = __id, 'Table'[Admission Date] = __date )Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on LinkedinHello,
I tried this formula and it seemed to error on the bold faced text i underlined. I tried switching it to Case# but did not get any luck as well. I might be missing something however.
Measure = VAR __id = MAX ( 'Table'[Case#] ) VAR __date = CALCULATE ( MAX( 'Table'[Admission Date] ), ALLSELECTED ( 'Table' ), 'Table'[Case#] = __id ) RETURN CALCULATE ( sum ( 'Table'[Score] ), VALUES ( 'Table'[Case#] ), 'Table'[id] = __id, 'Table'[Admission Date] = __date )
That need to be case #
Measure = VAR __id = MAX ( 'Table'[Case#] ) VAR __date = CALCULATE ( MAX( 'Table'[Admission Date] ), ALLSELECTED ( 'Table' ), 'Table'[Case#] = __id ) RETURN CALCULATE ( sum ( 'Table'[Score] ), VALUES ( 'Table'[Case#] ), 'Table'[Case#] = __id, 'Table'[Admission Date] = __date )
Hi Anonymous ,
You can try this measure and put it in filter:
Visual Control =
IF (
SELECTEDVALUE ( Admissions[Admission Date] )
= CALCULATE (
MAX ( Admissions[Admission Date] ),
FILTER (
ALLSELECTED ( Admissions ),
'Admissions'[Case#] IN FILTERS ( 'Admissions'[Case#] )
)
),
1,
-1
)
You will get your hoped result like this:
Here is the demo , please try it:
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Take case in a table or matrix and this as second column and check
