topic Re: Retrieve a date from another table where criteria is met in DAX Commands and Tips

Retrieve a date from another table where criteria is met

RichHead1821 — Fri, 07 Aug 2020 10:48:54 GMT

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.

Re: Retrieve a date from another table where criteria is met

Anonymous — Fri, 07 Aug 2020 12:07:50 GMT

Hi RichHead1821 ,

Can you share some sample date.

Regards,

Harsh Nathani

Re: Retrieve a date from another table where criteria is met

RichHead1821 — Fri, 07 Aug 2020 12:39:30 GMT

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

Re: Retrieve a date from another table where criteria is met

Anonymous — Fri, 07 Aug 2020 12:46:22 GMT

Hi RichHead1821 ,

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

Regards,

Harsh Nathani

Re: Retrieve a date from another table where criteria is met

RichHead1821 — Fri, 07 Aug 2020 12:54:57 GMT

How do I share a PBIX file here?

Re: Retrieve a date from another table where criteria is met

RichHead1821 — Fri, 07 Aug 2020 12:57:19 GMT

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

Re: Retrieve a date from another table where criteria is met

Anonymous — Fri, 07 Aug 2020 13:04:33 GMT

Hi RichHead1821 ,

Try One Drive or Google Drive.

Regards,

Re: Retrieve a date from another table where criteria is met

RichHead1821 — Fri, 07 Aug 2020 13:06:41 GMT

This is T1

This is T2

Relationships

Re: Retrieve a date from another table where criteria is met

amitchandak — Fri, 07 Aug 2020 13:36:28 GMT

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

Re: Retrieve a date from another table where criteria is met

RichHead1821 — Fri, 07 Aug 2020 13:38:39 GMT

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

Re: Retrieve a date from another table where criteria is met

RichHead1821 — Tue, 11 Aug 2020 09:09:34 GMT

Solution remove the 'SELECTEDVALUE' from the DAX:

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