Find Most recent Record with Score

[Anonymous]

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!

[v-yingjl]

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:

PBIX 

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.

[Anonymous]

Hello, when i am trying this it seems to be only returning the most recent record for one case # not all. 

[amitchandak]

Take case in a table or matrix and this as second column and check

[Anonymous]

I see now. Is there a way to SUM that total now? So it pulled both scores in for 35 however itsnt totaling the 2 most recent records i tried up. I appreciate all the help.

[amitchandak]

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 Linkedin

[Anonymous]

Hello,

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 )

[amitchandak]

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 )

[Anonymous]

I see so i did have it right. However when i am putting it into a table visual i am getting a cant display visual message. It would not SUM string values.

[amitchandak]

Check the datatype of score, it should be whole number. else use max

Measure = 
VAR __id = MAX ( 'Table'[Case#] )
VAR __date = CALCULATE ( MAX( 'Table'[Admission Date] ), ALLSELECTED ( 'Table' ),  'Table'[Case#] = __id ) 
RETURN CALCULATE ( max( 'Table'[Score] ), VALUES ( 'Table'[Case#] ), 'Table'[Case#] = __id, 'Table'[Admission Date] = __date )

[Anonymous]

This does return one record value for me however i need all of the case #s most recent scores not just the one. 😕 I apologize if i didnt say that before.