Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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:
Power Bi multiplying columns in different tables
Best Regards
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |