Retrieve a date from another table where criteria is met

[RichHead1821]

Hi,

I have two tables:

T1 holds:  interviewID, status, statusvalue & statusdate

T2 holds: interviewID, lots of other columns and a new column created by DAX which I want to store a date when a certain status & statusvalue is found in T1.

T2 new column measure = 

LOOKUPVALUE('T1'[StatusDate],'T1'[interviewId],SelectedValue('T2'[interviewId]),T1[status],"Quoted",T1[statusvalue],"Complete")

I can see there are entries in T1 with interviewId which match entries in T2 and that T1 has lots where the status = Quoted and th statusvalue = Completed; but the DAX never returns a date (it does not error, just never seems to find the matches and return a date).

As I thought this was the easy part of what I'm building it has left me somewhat discombobulated.

[harshnathani]

Hi @RichHead1821 ,

Can you share some sample date.

Regards,

Harsh Nathani

[RichHead1821]

No, as the data is commercially sensitive.

The two IDs are wholenumbers, the status and statusvalue are text and the date to return and the new column are both datetime

[harshnathani]

Hi @RichHead1821 ,

Try creating some dummy values. i can try but you know how the data looks like 🙂

Regards,

Harsh Nathani

[RichHead1821]

How do I share a PBIX file here?

[RichHead1821]

Hi @harshnathani - I have an example PBIX file which demonstrates this (with dummy data) - but have no idea how to share that data

[RichHead1821]

This is T1

This is T2

Relationships

[amitchandak]

@RichHead1821 , I do see any date in T1. But any date in T1 in slicer can filter T2

[RichHead1821]

I am trying to create the date in T1, using the DAX in the first screenshot...

[RichHead1821 · Accepted solution]

Solution remove the 'SELECTEDVALUE' from the DAX:

T2 new column measure = LOOKUPVALUE('T1'[StatusDate], 'T1'[interviewId],' T2'[interviewId], T1[status],"Quoted", T1[statusvalue],"Complete")

[harshnathani]

Hi @RichHead1821 ,

Try One Drive or Google Drive.

Regards,

HN