Get most recent values on new table column

satlasg

Could you please try my version.  It appears you have a ton of rows, so I am swapping iteration with the set logic.
By the way,  the code works both as calc column and a measure.

=
CALCULATE (
    MAX ( Table1[Value] ),
    INTERSECT ( 
    		VALUES ( Table1[ID] ), 
    		VALUES ( Table2[ID] ) 
	)
)

Thanks, Nick -

Works as a Measure, does not stuck, however i am not sure i can utilize it this way, will check, thank you.

---

Zubair_Muhammad wrote:

satlasg

 

Try this as a MEASURE in table B

 

Value latest =
VAR RecentDate =
    CALCULATE ( MAX ( TableA[Date] ) )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( TableA[Value], 1 ),
        FILTER ( TableA, TableA[Date] = RecentDate )
    )

---

nickchobotar tried your solution, it does not bring the latest Value as per Date, only the first it finds. Need to work one that also distinguishes bettwen dates and takes the latest one to bring in the corresponding value.

---

nickchobotar wrote:

satlasg

Could you please try my version.  It appears you have a ton of rows, so I am swapping iteration with the set logic.
By the way,  the code works both as calc column and a measure.

 

=
CALCULATE (
    MAX ( Table1[Value] ),
    INTERSECT ( 
    		VALUES ( Table1[ID] ), 
    		VALUES ( Table2[ID] ) 
	)
)

 

 

Thanks, Nick -

---

satlasg

Please try this option. Works both as calc column and measure.

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

Thanks, Nick -

nickchobotar

I am getting the following when using the formula you provided as Column:

---

Please try this option. Works both as calc column and measure.

 

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

 

 

 

---

satlasg

Please make sure you have no ID duplicates in your Table B( Table 2)

Thanks, N -

nickchobotar

There are no ID duplicates in Table B (Table 2), i double-checked through Count and Distinct Count, they have the same number as a result. So that is not the issue.

Keep looking around, thanks.

satlasg

Are you relating both tables on the ID field  or you have other keys ?

Any chance, we can see the model diagram ?

N -

nickchobotar

The tables are related only on the ID field, Table A to Table B, Many to One, Single. 

So far only the "as a MEASURE" in Table B solution from Zubair_Muhammad seems to work,

---

Zubair_Muhammad wrote:

Try this as a MEASURE in table B

 

Value latest =
VAR RecentDate =
    CALCULATE ( MAX ( TableA[Date] ) )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( TableA[Value], 1 ),
        FILTER ( TableA, TableA[Date] = RecentDate )
    )

---

but again i need a Column, to be able to produce SUMS etc.

satlasg

Quick clarification. I understand that DAX code that I wrote does not work as calc colum, but does it work as measure in your model ?

nickchobotar

From what i tested, it does not work as a measure either. When trying to use it on a visual, i am getting:

"Couldn't load the data for this visual. Calculation error in measure, a table of multiple values was supplied, while a single value was expected"

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 -

nickchobotar you are right, apologies.

However, i have tested your code and that is where i ended:

---

satlasg wrote:

nickchobotar

 

I am getting the following when using the formula you provided as Column:

 

 

 

---

Please try this option. Works both as calc column and measure.

 

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

 

 

 

 

---

 

---

I also checked the IDs and there are no duplicates., cannot figure where the "single value was expected" comes from.

Thanks for the pbix file.

Will try to implement the M code and see where it ends.

G