Dealing with multiple indirect relationships in the model

[daffodillfern]

Hi all

Some of you may have seen my post a few days ago about problems I'm having with multisite quotes. I'm looking at things from a different angle and have gotten stuck again.

I'm using Salesforce data, and reporting on the value of quotes issued to customers. We have several locations, and I have a locations table linked to my quotes table so that Location can be used as a slicer in visualisations. There are a handful of quotes that involve work by more than one location, and these have the Location set to "Multi". The business would like to be able to attribute a portion of the "Multi" value to each relevant location and roll these up into a measure with the value originally attributed to each location, but still retain the ability to report on "Multi" and non-rolled up values.

My plan is to have the portion assigned to each location stored in a field on the quotes record, then hold these values in a seperate table in Power BI, linked to the original quotes record. I've figured out how to unpivot the tables and store the values so that they look like:

Quote ID	Multisite Location	Value
AA123	Location1	100
AA123	Location2	100
BB124	Location1	200

I've decided to create 3 tables to hold my quotes values for each location, and join them to a master quote table that has the rest of the facts for each quote. The 3 value tables link to a location table, and the master table links to the opportunities table. I've successfully created the measures I need using a USERELATIONSHIP clause to call the correct link to the location table.

The problem comes when I try to put this into my large data set. I have a lot of dates in my larger model, so I have a calendar table and I use USERELATIONSHIP in my measures to pick up the correct one each time. I don't want to lose the ability to report on quotes in the same axis/slicers as opportunities - can anyone see a clever way out of this? Here's a snap of my smaller model:

Many thanks 

df

[daffodillfern]

Hi Rena

Thank you for your response, but the issue is that I have 2 indirect relationships - I need to slice my data by location and date at the same time, and can't use USERELATIONSHIP to make both relationships active at the same time.

[v-yiruan-msft]

Hi  @daffodillfern,

You can refer the following links to get it:

Use LOOKUPVALUE:

Retrieve Multiple Columns From Another Table

Power Bi multiplying columns in different tables

Best Regards

[v-yiruan-msft]

Hi @daffodillfern ,

You can refer the following links to get it:

How To Work With Multiple Dates In Power BI

UseRelationship or Role-Playing Dimension; Dealing with Inactive Relationships in Power BI

Multiple Relationships Between Tables in DAX

Best Regards