Looking up the value in related table based on the first/last aggregation

[redagad13]

Hello,

I have one table containing a document name and the elements contained in the document:

Document    ObjectName
Document 1  Task A

Document 1  Task B

Document 2  Task A

Document 3  Task A

Document 3  Task  C

I have another table containing a unique list of elements (ObjectName)

Object

Task A

Task B

Task C

Both tables are related using the unique identifier column of elements.

The first table is filtered by the user using a slicer to select 2 document names: For example, when selecting Document 1 and 3, I I want to display the unique list of elements and create an additional column (Source) that will indicate if the element is present in the first selected document, the second (last selected document or both).

Desired Result (when selecting Document 1 and 3 from the slicer):

Object  Source
Task A   Document 1 and 3

Task B Document 1

Task C   Document 3

 

Any help would be greatly appreciated!

Thanks!

[redagad13]

Oh, this is great! Thanks very much @lbendlin 

[lbendlin]

Please explain the reason for not showing Task B in the result - it is linked to one of the documents.

[redagad13]

Thanks for you reply. It should be returned, that was a mistake from my side. I amended the original post.

[lbendlin]

This logic should work for any number of documents and will show the correct spelling (for varying degrees of correct).  See attached.

Apologies for the DOM joke.