Get most recent values on new table column

[satlasg]

Hello, 

I have two tables A & B with "many to one" relationship, as below:

Table A              Table B
I am looking for a formula to fill in 'Table B'[Value Latest] based on the Value from the more recent Date the ID has from Table A.
Any ideas please?

[Greg_Deckler]

Here is one way:

Column = MAXX(RELATEDTABLE(IDValues),[Date]) 

[satlasg]

It brings a result, but a date, not a Value that is needed, for the most recent date.

[Greg_Deckler]

Apologies:

Column = LOOKUPVALUE('IDValues'[Value],'IDValues'[Date],MAXX(RELATEDTABLE(IDValues),[Date])) 

[satlasg]

---

Column = LOOKUPVALUE('IDValues'[Value],'IDValues'[Date],MAXX(RELATEDTABLE(IDValues),[Date])) 

---

Dear @Greg_Deckler

While that seem to work on the small tables i provided, in the large scale tables (>500K rows) it cannot calculate, says "working on it" and after a while a message "out of memoy, please try later" appears.

Any turnaround please?

[Greg_Deckler]

OK, I got rid of the MAXX, see if this works better.

Column2 = 
var myMax = CALCULATE(MAX(IDValues[Date]),RELATEDTABLE(IDValues))
var myID = [ID]
RETURN LOOKUPVALUE('IDValues'[Value],'IDValues'[Date],myMax,'IDValues'[ID],myID)

[satlasg]

Hi @Greg_Deckler this formula treats memory better, however it does not bring a result, PowerBI desktop only displays "Working on it" and stucks. On the task manager i see it does not affect the memory that much. The CPU goes to 25-26% for the process MSFT SQL Server Analysis Services and keeps working. Cannot yet figure out what might be wrong.

---

@smoupre wrote:

OK, I got rid of the MAXX, see if this works better.

Column2 = 
var myMax = CALCULATE(MAX(IDValues[Date]),RELATEDTABLE(IDValues))
var myID = [ID]
RETURN LOOKUPVALUE('IDValues'[Value],'IDValues'[Date],myMax,'IDValues'[ID],myID)

---

[Zubair_Muhammad]

@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 )
    )

[satlasg]

@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.

[nickchobotar]

@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 ?

[satlasg]

@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"

[nickchobotar]

@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

[satlasg]

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

[nickchobotar]

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 -

[thomasronn]

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? 

[nickchobotar]

Hi @thomasronn

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

N -

[nickchobotar]

@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

[satlasg]

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]

@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]

@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 -

---

[nickchobotar]

@satlasg

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

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

 

Thanks, Nick -