Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
daffodillfern
Frequent Visitor

Dealing with multiple indirect relationships in the model

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 IDMultisite LocationValue
AA123Location1

100

AA123Location2100
BB124Location1200

 

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:

 

daffodillfern_0-1653556581730.png

Many thanks 

 

df

3 REPLIES 3
daffodillfern
Frequent Visitor

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.

Hi  @daffodillfern,

You can refer the following links to get it:

Use LOOKUPVALUE:

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yiruan-msft
Community Support
Community Support

Hi @daffodillfern ,

You can refer the following links to get it:

How To Work With Multiple Dates In Power BI

yingyinr_0-1653892487640.pngyingyinr_1-1653892535953.pngUseRelationship or Role-Playing Dimension; Dealing with Inactive Relationships in Power BI

Multiple Relationships Between Tables in DAX

Best Regards

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.