Forum Discussion

satlasg's avatar
satlasg
Icon for Helper I rankHelper I
8 years ago

Get most recent values on new table column

Hello,  I have two tables A & B with "many to one" relationship, as below:                I am looking for a formula to fill in 'Table B'[Value Latest] based on the Value from the more...
Need Help
nickchobotar's avatar
nickchobotar
Icon for Skilled Sharer rankSkilled Sharer
8 years ago

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's avatar
satlasg
Icon for Helper I rankHelper I
8 years ago

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's avatar
    nickchobotar
    Icon for Skilled Sharer rankSkilled Sharer
    8 years ago

     

    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 -