Power BI model

[Alexh84]

just wondering if someone has a solution to this. I'll try my best to summarise. I have a table that holds referral level data - the referral ID is unique. I use this table to build out a table visual from the values. The table is connected to another table (reported outcomes) by the referral ID. The relationship is one to many, one on referral and many on reported outcomes. I want to drill through the outcome table on referral ID, but also bring in certain values from the outcome table into the table visual such as first outcome completed for referral etc - but the filter context is flowing the other way. I don't think a bidirectional relationship is the way to go - maybe a bridge table or can I use some DAX measures? 

[johnbasha33 · Accepted solution]

Hi @Alexh84 

Your setup:

• Referral Table (Referral ID is unique — 1 side)

• Reported Outcomes Table (Multiple rows per Referral ID — many side)

Relationship:

Referral (1) ----> (Many) Reported Outcomes

Get the first completed outcome:
First Outcome Completed =
CALCULATE(
MIN('Reported Outcomes'[Outcome Completed Date]),
'Reported Outcomes'[Referral ID] = SELECTEDVALUE('Referral'[Referral ID])
)
or if you want the corresponding outcome value:
First Outcome Name =
VAR FirstDate =
CALCULATE(
MIN('Reported Outcomes'[Outcome Completed Date]),
'Reported Outcomes'[Referral ID] = SELECTEDVALUE('Referral'[Referral ID])
)
RETURN
CALCULATE(
MAX('Reported Outcomes'[Outcome Name]),
'Reported Outcomes'[Outcome Completed Date] = FirstDate,
'Reported Outcomes'[Referral ID] = SELECTEDVALUE('Referral'[Referral ID])
)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!