Get most recent values on new table column

satlasg

Works like a Swiss Watch on my end. As you can see below both cacl column and measure work inside a visual too.

At this point, I would recommend you to try to implement the DAX I offered to you on a small sample of your original data set.  See if it you can replicate it and get the results back. Then, gradually start adding table by table and test the calc column as you progress. In this way, you will be able to spot the reason why it's not working on your end.

*** Also, please make sure that your ID field is set to the text data format if you intend to use this field on the visual axis

If possibile, please post the diagram of your whole model

The "...multiple values was supplied, while a single value was expected" error is because for some of your ID's there are multiple entries with 'latest date'. Which entry should give the value when you have multiple with same id and date? the highest value? the lowest? or? 

Hi thomasronn

Nope. That's not  the case. My DAX works with the duplicate scenario you have brought up.

N -

satlasg

Not sure where you are with your progress, I hope the DAX recipes that were offered to you were helpful. 

I can see this to be a quite common business requirement, so I decided to post the solution in M code too.

      = Table.AddColumn(#"Changed Type", "M Code",  
        (x) =>   
            List.Last(
                Table.Column( 
                    Table.SelectRows(
                        Table.Sort(Table1,"Date"),
                         each[ID] = x[ID] 
                   ), "Value"
                )
            ), type number
        )

Example Source data:

Table 1
ID	Value	Date
34	29	12/17/2017
34	29	12/17/2017
34	29	12/17/2017
34	29	12/17/2017
34	29	12/17/2017
12	26	12/15/2017
15	65	12/29/2017
45	100	12/23/2017
45	1	12/24/2017
12	94	12/25/2017
34	29	12/17/2017
15	41	12/27/2017
34	29	12/17/2017

Table 2

ID
12
34
45
15

Hi nickchobotar

I tested your DAX formula in a sample, and this is what i got:

and based on the sample:

is not working, doesn't bring the values for the latest dates. 

Also, testing this formula in the large database, is not performing, is too demandind in terms of memory i guess, will need another approach maybe.

As for the M solution posted above, i have to try it out, never used M but i will now and revert.

Thanks

G

Hi satlasg

The DAX you posted above is not my code. Pls see below the code I posted for you to try.

Theoretically, it should be fast since we have here only one implicit FILTER() iteration.  I am also posting here a pbix file for you with both solutions DAX and M.

Give it go, here is the link

https://1drv.ms/u/s!AsgNvkRwqGC7gx4VhRC1tCNyk4Or

CalcColumn= 
CALCULATE(
    VALUES(Table1[Value]),
    LASTDATE( Table1[Date])
)

Nick -