Forum Discussion

joefusaro's avatar
joefusaro
Frequent Visitor
8 years ago

Calculating minimum date filtering by 2 related tables

I have a database containing Campaigns, People, Accounts and Opportunities (pending, won & lost sales). Campaigns are related to people via the CampaignMember table to facilitate a many-to-many relationship between People and Campaigns.

 

 

I am building a report in Power BI where a user will select a single Campaign from a slicer. 

 

On the Person table, I would like to create a calculated column that returns the FirstRespondedDate for each Person, based on the CampaignMember that is related to that Person AND the selected Campaign. Since a single Campaign will be selected, the data should now be filtered to a 1:1 relationship between CampaignMember and Person.

 

First I tried:

 

```
MinFirstRespondedDate = CALCULATE(
MINX(CampaignMember, CampaignMember[FirstRespondedDate]
)
```

 

But this did not work as it returns the Minimum First Responded Date for ALL CampaignMembers related to that Person, not just the CampaignMember related to the selected Campaign and the Person.

 

Can anyone recommend a DAX equation to accomplish what I'm looking for?