1 to many relationship - want to show top row foreign key as column

[jthake-msft]

I have a scenario where I have a table with a 1 to many relationship

Questions

QuestionId - Title

QuestionsWorkloadId

QuestionID - WorkloadId - WorkloadAssignmentDate

In the Questions table, I would like to have a column added to show the workloadId for the row with the most recent WorkloadAssignmentDate.

I was looking into using something like 

CurrentWorkloadId = CALCULATE(TOPN(1,QuestionWorkloadAssignments, QuestionWorkloadAssignments[WorkloadId], ASC))

But I can't seem to work out how to get it to work . Could someone help me please?

[ImkeF · Accepted solution]

Hi @jthake-msft ,

try this:

MAXX(
    TOPN(
        1,

        RELATEDTABLE ( QuestionWorkloadAssignments),

        QuestionWorkloadAssignments[Date],

        ASC),
    QuestionWorkloadAssignments[workloadId] )

[jthake-msft]

Thank you.

If the Workload table had a WorkloadName

Is there a way to output that in the column rather than the WorkloadId?

As I can't seem to add a relationship to this column to a table, so that I can create a chart that shows a breakdown of number of questions by Workload name 

[ImkeF]

Hi

I might be missing something here, but wouldn' t this work?:

MAXX(
    TOPN(
        1,

        RELATEDTABLE ( QuestionWorkloadAssignments),

        QuestionWorkloadAssignments[Date],

        ASC),
    QuestionWorkloadAssignments[Name] )

[jthake-msft]

No the name of the workload is in the Workload table not the QuestionWorkloadAssignments table. In the formula, it can't resolve Workload[Name], I'm guessing because it isn't the related table. Although QuestionWorkloadAssignments does have a retionship with the Workload table.

Questions

QuestionId - Title

QuestionsWorkloadAssignments

QuestionID - WorkloadId - WorkloadAssignmentDate

Workload

WorkloadId - WorkloadName

What I did was create a new Column basically adding WorkloadName to the QuestionsworkloadAssignments table. And then used this in the Questions table for CurrentWorkloadName column based on your formula. Thank you!