Lookupvalue brings in blank value

[Anonymous]

Hi!

I am facing some issue with teh LOOKUPVALUE results. Basically I have master Table with ID's and Date and I have another Table with ID 1 , ID 2, Date 1 & Date 2 as columns. The Relatuonship between Table 1 and Table two is on ID-> ID 1, so I am getting the Date 1 Populated with the relationship . I wanted to fill out the Date 2 column using the LOOKUPVALUE but unfortunately its bringing in blank. 

Table 1(Master).

ID                         Date

ENCGNRL03280	1/3/2019
ENCGNRL03290	2/4/2020
ENCGNRL03300	3/5/2019
ENCGNRL03310	4/4/2019
ENCGNRL03320	6/3/2020
ENCGNRL03330	6/3/2021
ENCGNRL03340	3/7/2019
ENCGNRL03350	3/7/2020
ENCGNRL03360	4/5/2021
ENCGNRL03370	7/4/2020

  Table 2 

ID 1	ID 2	Date 1	Date2
ENCGNRL03280	ENCGNRL03330	1/3/2019	??
ENCGNRL03290	ENCGNRL03340	2/4/2020	??
ENCGNRL03300	ENCGNRL03350	3/5/2019	??
ENCGNRL03310	ENCGNRL03360	4/4/2019	??
ENCGNRL03320	ENCGNRL03370	6/3/2020	??

LOOKUPVALUE(Date, ID, ID2), results in Blank, whereas it should have filled the dates.

help will be greatly appreciated

Thanks

[amitchandak · Accepted solution]

@Anonymous , One of these two should work

Both as new columns

Date 2 = minx(filter(table2,[ID 1] =earlier([ID 2])),[Date1])

Date 2 = minx(filter(table1,table1[ID ] =(table2[ID 2])),table1[Date])

[amitchandak · Accepted solution]

@Anonymous , One of these two should work

Both as new columns

Date 2 = minx(filter(table2,[ID 1] =earlier([ID 2])),[Date1])

Date 2 = minx(filter(table1,table1[ID ] =(table2[ID 2])),table1[Date])

[parry2k]

@amitchandak understand there are alternative solutions but the question is why lookupvalue function is not working. 

[parry2k]

@Anonymous looking at the table view, it should return,

- can you make sure ID2 doesn't have extra space etc which is not letting it match

or 

- create relationship on ID with ID2 and see if you get date1, just for testing.

[Anonymous]

Hi!

Well there is no space on ID2, I ahve checked that. As for chnaging the relationship from ID to ID2, I get the Date 2 now and miss out the Date 1, its doing exactly the opposite and I cant have both the relationships active  .i.e ID ->ID1 and ID->ID2.

Thanks 

[parry2k]

@Anonymous As I mentioned in my previous reply was to check if we get the dates when switch relation to ID2 so that we can test if we get the date value and seems like it is working? What expression you are using to get the date value, RELATED ( Table1[Date] ), is this what you are using?

[Anonymous · Accepted solution]

I was using the LOOKUPVALUE function itself. I think I have figured out and it does work in both the scenerios.

Thanks for your help on this.

[parry2k]

@Anonymous aha, I thought you have correct column name in the lookupvalue as you never put the table name,  never ever use anything without table name for columns and never use table name with measures, it is a best practice. This would have been solved quickly if I noticed the Id column is not from the correct table name.

Anyhow good to hear it is solved, that's all matter.