Forum Discussion
Userelationship with if function
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!
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
6 Replies
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
dirkkoch Maybe CALCULATE(MAX('Table B'[Date]),USERELATIONSHIP('Table A'[Value],'Table B'[Value]))
Greg_Deckler Unfortunately then it only writes back the latest date but not the exact date listed in table B.
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
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.
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] ) )
