I have a powerbi model that looks like the following:

The Candidate & Validation tables look like:

I have a drill down feature that will allow for the user to drill down on the ID from a table visual with candidate data to another table visual with Validation data.

I’ve hard-coded the ExpectedTopID, but what I need is a measure. The measure will be used in the validation table visual to get ID from the candidate table where Rank = 1 and the group is the same.

So if I drilled down from the candidate visual on ID 222, I would see ExpectedTopID 111 because the Candidate.Group for ID 222 is a, and then if I get the Candidate.ID where Group = a AND Rank = 1 I return ExpectedTopID 111.

The DAX I’m using to try and produce this gets the current group from the candidate table in a variable then tries to get the ID from candidate where Rank = 1 and group = group. I’m using MAX in the expression to make sure its only one value returned although it shouldn’t really be an aggregate since there will only ever be one value with Rank = 1 per group.

ExpectedTopIDMeasure = 
VAR grp = SELECTEDVALUE(Candidate[Group])
RETURN 
 CALCULATE(
    MAX(Candidate[ID]),
    FILTER(
        ALL(Candidate),
        Candidate[Group] = grp && Candidate[Rank] = 1
    ) 
)

However, when I use this code, the only time there is any data in the validation table is when the ID = ExpectedTopID (e.g. in this case the drill down only works for ID 111 since it has a Rank of 1 for group A )

What am I doing wrong here? Its obviously filtering in a way that the ID has to be equal to the topID for the group to work, but I need it to be visible for all ID’s in the group?