Forum Discussion
Post PatronDAX 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
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.
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
kattleesI 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?
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