Userelationship with if function

[dirkkoch]

Hi,

I have two tables A & B with a many to one relationship.  This relationship cannot be activated due to activation with another table C.

What I want to do is to create the new column marked in yellow by activating the relationship and comparing value from Table A with value from Table B. If equal, then write Date from Table B in New Column in Table A. I am looking for your support. Thank you in advance!

[v-jayw-msft · Accepted solution]

Hi @dirkkoch ,

You could try lookupvalue() function.

https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax 

column = lookupvalue('tableB'[date],'tableB'[value],'tableA'[value])

Best Regards,

Jay

[Samarth_18]

Hi @dirkkoch ,

Create a column with this code:-

Column =
CALCULATE (
    MAX ( 'Table B'[Date] ),
    FILTER ( 'Table B', 'Table B'[Value] = 'Table A'[Value] ),
    USERELATIONSHIP ( 'Table A'[Value], 'Table B'[Value] )
)

Output:-

Thanks,

Samarth

[dirkkoch]

@Samarth_18 thank you, we are getting closer to a solution. Most of the dates are returned correctly, but still some blanks are left, although the values appear in both tables and a date would be expected. But the rest of the dates are correct.

[Samarth_18]

@dirkkoch Is it possible for you to share PBIX file after removing sesitive data?

In the mean time you can try below code:-

Column =
CALCULATE (
    MAX ( 'Table B'[Date] ),
    FILTER ( 'Table B', TRIM ( 'Table B'[Value] ) = TRIM ( 'Table A'[Value] ) ),
    USERELATIONSHIP ( 'Table A'[Value], 'Table B'[Value] )
)

[dirkkoch]

@Greg_Deckler Unfortunately then it only writes back the latest date but not the exact date listed in table B.

[Greg_Deckler]

@dirkkoch Maybe CALCULATE(MAX('Table B'[Date]),USERELATIONSHIP('Table A'[Value],'Table B'[Value]))