Measure to filter out non-required records

[MCosenza]

Hi everyone, I am using a dataset which holds data for around 100k of projects where these projects will have multiple parts of the business providing funding for them. I'm trying to create a measure to filter out the business funders I do not need and only bring back the lines I do need to see as at the moment when I filter I am seeing all the results for any given project I look at. I have attached a simple example table where for a project with multiple business funders, I only want to see the record highlighted in yellow. Any thoughts on how to achieve this? Many thanks.

[HotChilli]

Not really.  I want to know "what is the logic behind selecting the row in yellow" ?

---

Also, is direct query the best method to use for this?

[MCosenza · Accepted solution]

I managed to get a count formula to give me what I need. Where there is a match on the item I see 1 else I see 0 for the records I do not need. Here's the formula just in case for reference: 

Count = CALCULATE(COUNTROWS('My Project List'),TREATAS(VALUES('My Project List'[Funder CODE]),'Main Porject List'[Funder CODE]))

[MCosenza]

Unfortunately I have no control over the direct query. It's what been setup by our central data centre so I have to use that. In terms of getting the yellow row, I have imported a table from excel which holds my list of project numbers and I'm using a slicer which is using this list to filter the main table of data to show my output which is including all funders. Annoyingly due an existing relationsip put in by the cetral team I'm not able to create a unique key based on project number and business funder which was what I was hoping to create.

[HotChilli]

You'll have to explain how powerbi would know "for a project with multiple business funders" .

e.g. do we have to count the different 'funded by' and find the record with the max amount?

[MCosenza]

I have a list of projects which I search for. The database holding this data has been set up with a relationship to return all the associated funders against a project ID. As this is a locked down direct query, I am unable to edit to discount what I don't need. I hope that helps explain the workings.