DAX help - Get latest value for each ID

[kattlees]

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 Code	Date Entered	Time Entered	Answer
00242998	ORDrop1	1/15/2019	2:42:00 PM	1438
00242998	ORStop1	1/15/2019	3:01:00 PM	1450
00242998	OROut1	1/15/2019	3:01:00 PM	1453
00242998	ORDate1	1/16/2019	2:37:00 PM	011519
00242998	ORDate1	1/15/2019	1:30:00 PM	11519
00242998	ORRoom1	1/15/2019	1:30:00 PM	OR 11
00242998	ORSchTm1	1/15/2019	1:30:00 PM	1215
00242998	ORInRm1	1/15/2019	1:30:00 PM	1255
00242998	ORVerify1	1/15/2019	1:30:00 PM	1256
00242998	ORReady1	1/15/2019	1:30:00 PM	1321
00242998	ORPause1	1/15/2019	1:30:00 PM	1327
00242998	ORStrt1	1/15/2019	1:30:00 PM	1328
00242998	OR1stMD1	1/15/2019	1:30:00 PM	Smith, Joe
00242998	OR1Asst1	1/15/2019	1:30:00 PM	Doe, Jane
00242998	ORCRNA1	1/15/2019	1:30:00 PM	Sue, Sally
00242998	ORCirc1	1/15/2019	1:30:00 PM	Noname, Nancy
00242998	OR1stST1	1/15/2019	1:30:00 PM	Whoever, Walter
00242998	OR1Proc1	1/15/2019	1:30:00 PM	Did surgery
00242998	ORDrop1	1/15/2019	2:13:00 PM	1412

[v-yulgu-msft]

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
)

Best regards,

Yuliana Gu

[Anonymous]

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.
            

[Anonymous]

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]

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?

[Anonymous · Accepted solution]

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

[AlB]

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]

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.