Ideas on how to contruct my model

[EpicTriffid]

Hi all,

I'll try and keep this simple. We have a process where a request is made for spend. Once approved, the person pays for the item on either our Procurement, Travel, or Purchase Cards systems, depending on the spend requested. Once receipted, it appears on a Receipted system. 

I'm trying to show a full pipeline of spend, and am struggling how to construct the relationships. This is an example of the structure I've got so far:

The Request system generates an ID that goes into the Procurment, Travel, Purchase Cards system. The output of those creates an ID that I can then reference in my Receipted system. The only unique set of rows is in the Requests system, as transactions can be split out many times in the other systems.

What I want is for the Receipted system to filter all the way back to the Request system. 

Any ideas? Happy to provide more info if needed, but I can't share the dataset or pbix file as it's private data. 

Thank you!

[v-cgao-msft]

Hi @EpicTriffid ，

Taking the three tables 'SpendRequests', 'Travel', and 'Receipted' as an example, you could create an intermediate table in 'Travel' and 'Receipted' like this:

I cannot determine the type of filtering you are attempting based on the above description.
Assuming it's an internal filter in code, you could calculate how many records in the 'SpendRequests' table match the filter like this:

Measure = CALCULATE(COUNTROWS('SpendRequests'),TREATAS(VALUES('Travel'[RequestID]),'SpendRequests'[RequestID]))

Measure 2 = CALCULATE(COUNTROWS('SpendRequests'),CROSSFILTER('SpendRequests'[RequestID],'Travel'[RequestID],Both))

You can also change this part of the relationship to both-direction, but this reduces model performance.

Of course you can also achieve some complex filtering with slicers + measure + visual filters, depending on what you want to achieve.
Here is a key modeling guidance:
Many-to-many relationship guidance - Power BI | Microsoft Learn

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[EpicTriffid]

Hi @v-cgao-msft 

Thanks for the response! So creating a mid table that just contains the (in your example) travel IDs? what does this achieve? Is it possible to link the requests table through to the receipted table doing that with all three systems tables?