Countrows with multiple related filters

[Gingerjeans88]

Hi all,

I am a bit of a DAX novice and am after some help with a measure on my Contacts table, with multiple conditional filters from two related child tables. The data source is Dynamics 365. 

The tables are: Contacts, who can have multiple Campaign Responses, and the campaigns those responses are related to have an option set field called Open Day Type... with specific values I want to filter on. 

Snippet of relationhips below - the Open Day Type option set has been brought in as a separate table using the Service Root URL

I want to: see the number of contacts who have a campaign response, where the related campaign was an Open Day. For some reason none of my old faithfuls are returning the correct results.

[v-xicai]

Hi @Gingerjeans88 ,

For the relationship, recommend you create the star schema instead of circle schema, so you may delete the secondary relationships among these tables, and change the Cross filter direction of relationships among the these tables above from Single to Both , which will take these tables treated as a single table. Then when you make some changes or interact in someone table, the other table objects will return corresponding matched result.  See more:Create and manage relationships in Power BI Desktop .

Then you may create measure like DAX below.

Count contacts=

var v=SELECTEDVALUE('Open Day Type'[Open Day])

return

COUNTROWS(FILTER(ALLSELECTED(contacts), contacts[campaign response]=v))

Or could you share some data sample and expected output? You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

How to Get Your Question Answered Quickly

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Gingerjeans88]

Hi Amy, 

Thanks so so much for your response, and my apologies for the delay in replying! 

Unfortunately I can't make the recommended changes to the relationship because I have many more tables depending on the Contact table, and changing that cross filter direction will compromise that won't it?