Return Row Instance from Separate Table based on Filter Criteria from Current Table

[Anonymous]

I am building a report on Learning Completion for our Training teams to use.  I have 2 tables - one that contains all the Learning Assignments, and a second that contains all the Learning Completions.  I need to find a way to bring specific data from the Learning Completions table into the Learning Assignments table to show wether a Learner is compliant with their most recent learning assignments.  See tables and explanation below:

So, In the first table we see a bunch of information about training that has been assigned to specific Learners including Course name, Date Assigned and Due Date.  However, for us to know wether the assignment has been completed we need to pull data from the second table.  However, Learners may have multiple instances of having taken a course in the Learning Completions table.  So, for the 2 columns we'd like to populate in the Learning Assignments table (yellow) we need to pull the data from the Learning Completions table but only for the instances where the learner enrolled in the course on or after the date the course was assigned (from the Learning Assignments table). 

I can (and assume I should) create a calculated column in the Learning Assignments table, but I need to know what DAX to use to get it to pull the Completion Status and Completion Date for the specific instances in the Learning Completion table as described above.  Hope this all makes sense. Any help is much appreciated.

Thanks

[v-yuta-msft]

Hi Dmac11,

Based on your tables, a Many-to-Many relationship model may work in this senario. Please refer to the documentation: https://docs.microsoft.com/en-us/power-bi/desktop-many-to-many-relationships.

Regards,

Jimmy Tao

[Anonymous]

Thanks for the reply.

I did end up building a separate table with unique values for our Workers, so the tables are connected at this point.

The part I'm having trouble with is the DAX code for my calculated columns...

[v-yuta-msft]

Hi Dmac11,

If the two tables have relationship with each other. You can call other table's column directly in your measure or calculate column, for example. You can use DAX pattern as below:

Result =
CALCULATE (
    MAX ( Table1[Column1] ),
    FILTER ( Table2, Table2[Column2] > Value )
)

Regards,

Jimmy Tao

[Anonymous]

Thanks for the reply.

I guess I'm not sure exactly how that would help me...

In trying to return the completion date for the one specific instance I have gotten this far, but am stuck at the end of the DAX code.  I'm using 'LOOKUPVALUE' but can't narrow it down to the final instance using the Completion Date field.