Measure to retrieve dates from multiple data tables

[SMY]

I am struggling to find a solution to something and was wondering if anyone can help here ?

I have a table with a list of raw deal numbers that links to two underlying details of varying levels of granularity for each transaction.

I am trying to create a pivot that would show each transaction number and then return a “next date” using the following logic.

• Go to the more detailed tab and find the first date meeting a certain set of criteria for the transaction ID in question.

But

• If that first route doesn’t return a valid date, go to the summary detail tab and return the end date down there.

I have managed to create a type of measure that partially works for the first point but it then seems to filter the pivot to show only those transactions it's been able to find an answer. I've tried lots of variations of switch or if but can't seem to arrive at anything that might work.

At this point, I'm not sure if it's my formula or something fundamental I'm missing in terms of my data structure.

Can anyone offer any suggestions or advise as to what direction I need to go to resolve this?

[v-henryk-mstf]

Hi @SMY ,

What are the condition for valid date judgment? You can try to judge the filter valid date by the FILTER and IF functions, and select the summary table that meets the condition, otherwise go to the details table for further solution.

If the problem is still not resolved, whether you can provide test data and expected results, I will answer for you as soon as possible. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SMY]

Hi Henry. Thank you for you input. By "valid date", I mean non-blank. In certain cases, the first measure I've created returns only a blank value as there won't actually be a date in the details tab meeting the criteria set. In such cases I'd then like it to refer back to the summary table and retrieve the date from a particular column there referring to the Transaction ID in question. Does that clarify for you?