powerbidaxdata-analysispowerbi-desktopcalculated-columns

DAX - Making a calculated column that has another table's value, based on several factors

I am having trouble with a calculated column I am trying to develop. I have two tables:

(I don't know why the tables aren't displaying properly. It looks fine in preview)

Table A:

PK Calculated column
1 Value wanted in caluculated column row 1
2 Value wanted in caluculated column row 2

Table B:

PK Table A ID Wanted Value Date param
1 1 Value wanted in caluculated column row 1 2020/10/08
2 1 Value not wanted 2020/04/04
3 2 Value wanted in caluculated column row 2 2021/01/07
4 3 Value not wanted 2022/04/12
5 3 Value wanted in caluculated column row 3 2023/01/02

What I am stuck on is the DAX code to create a calculated column (Named "Calculated Column" in the example tables) that will take the value from the "Wanted Value" column in table B, based on which Table B row is the most recent regarding the "Date param" column value.

Any help is greatly appreciated. Thanks in advance.

PS: Please tell me if any additional details are required. Also, I normally provide my current code when asking questions on SO, but in this case, I don't even know where to start.

Solution

  • UPDATED

    Here you go:

    Calculated column = 
VAR m = CALCULATE( MAX('Table B'[Date param]))
RETURN
CALCULATE( MAX('Table B'[Wanted Value]), 'Table B'[Date param] = m)

    enter image description here

    enter image description here

    enter image description here