cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kattlees Member
Member

DAX help - Get latest value for each ID

I have a table (sample data below) that has

Customer #
Ans Code

Date Entered

Time Entered

Answer

 

What I am looking for is to get the LATEST answer nentered based on the Ans Code. In the example below, there are two entries for ORDate1. I only want to keep the one entered on the 16th.

 

 

Customer #Ans CodeDate EnteredTime EnteredAnswer
00242998ORDrop11/15/20192:42:00 PM1438
00242998ORStop11/15/20193:01:00 PM1450
00242998OROut11/15/20193:01:00 PM1453
00242998ORDate11/16/20192:37:00 PM011519
00242998ORDate11/15/20191:30:00 PM11519
00242998ORRoom11/15/20191:30:00 PMOR 11
00242998ORSchTm11/15/20191:30:00 PM1215
00242998ORInRm11/15/20191:30:00 PM1255
00242998ORVerify11/15/20191:30:00 PM1256
00242998ORReady11/15/20191:30:00 PM1321
00242998ORPause11/15/20191:30:00 PM1327
00242998ORStrt11/15/20191:30:00 PM1328
00242998OR1stMD11/15/20191:30:00 PMSmith, Joe
00242998OR1Asst11/15/20191:30:00 PMDoe, Jane
00242998ORCRNA11/15/20191:30:00 PMSue, Sally
00242998ORCirc11/15/20191:30:00 PMNoname, Nancy
00242998OR1stST11/15/20191:30:00 PMWhoever, Walter
00242998OR1Proc11/15/20191:30:00 PMDid surgery
00242998ORDrop11/15/20192:13:00 PM1412
1 ACCEPTED SOLUTION

Accepted Solutions
adetogni Member
Member

Re: DAX help - Get latest value for each ID

Mh not that easy. 

What you could try

1) duplicate your table

2) concatenate date and time

2) group by customer and ans code and find the Max date+time (this will generate a new table with only the row with max date for each customer+ans)

3) now use MERGE of these two tables using a inner join

 

SHOULD work, but have never tried

7 REPLIES 7
Super User
Super User

Re: DAX help - Get latest value for each ID

Hi @kattlees

 

Where would you want the result? In another table? On a visual? 

Is it enough with checking the Date column or would we have to take the Time column into account as well?

Do you want this for each specific customer that comes up in the table or is it across all customers?

 

kattlees Member
Member

Re: DAX help - Get latest value for each ID

The result will eventually wind up in a visual. I was thinking once I got only the latest values by ans_code then I would pivot the column so each ans code is it's own column and pull into the visual that way.

 

Dates could be the same. It is checking for mistakes. They could fix the mistake the same day.

adetogni Member
Member

Re: DAX help - Get latest value for each ID

I would use a calculated column with the RANKX function. This will add a column with a progressive number 1, 2, 3 etc of rows grouped by (with 1 being the latest). If I get it well, you have to group by customer# and Ans Code, sorting by Date Entered (i'm skipping the time for simplicity)

 

Add a colum and use this formula

rankedorder=
VAR thisCustomer=table[Customer#]
VAR thisAns=table[Ans Code]


RETURN
RANKX (
      FILTER (
           table,
           table[Customer#]=thisCustomer && table[ans Code]=thisAns),
      table[Date Entered],
     DESC)


(if you want 1 to be the first, use ASC  instead of DESC)

How it works? This is run for each row. It calculates the current customer and ans, then filter the table on these two values and set the rank based on the sort order.
            

adetogni Member
Member

Re: DAX help - Get latest value for each ID

Sorry i did not read that you also need time.

 

Then in place of 

table[Date Entered]

use 

table[Date Entered] + table[time Entered]

 

so the formula consider also time. You may need to juggle a bit with this part

kattlees Member
Member

Re: DAX help - Get latest value for each ID

I can do this in the table, but is there a way to do this in the power query BEFORE I pivot the table so I can wind up with one row for each Customer Number?

adetogni Member
Member

Re: DAX help - Get latest value for each ID

Mh not that easy. 

What you could try

1) duplicate your table

2) concatenate date and time

2) group by customer and ans code and find the Max date+time (this will generate a new table with only the row with max date for each customer+ans)

3) now use MERGE of these two tables using a inner join

 

SHOULD work, but have never tried

Community Support Team
Community Support Team

Re: DAX help - Get latest value for each ID

Hi @kattlees,

 

You can use a Matrix visual instead. 

 

Add below measure to visual level filter and set its value to 1.

rank =
RANKX (
    ALLEXCEPT ( 'Table', 'Table'[Customer #], 'Table'[Ans Code] ),
    CALCULATE ( MAX ( 'Table'[Date Entered] ) + MAX ( 'Table'[Time Entered] ) ),
    ,
    DESC,
    DENSE
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.