Showing results for 
Search instead for 
Did you mean: 
nanma94 Regular Visitor
Regular Visitor

Data model question




I have this simplified model (its a lot messier than thisSmiley Happy where on one report, geo country slicer drives visualizations that take measure from both measure tables down below, while on the other report, Customer slicer drives visualization thats also takes measure from both measure tables. 


My current data model kind of works but if I can have sales table to associate with Geo/country table  directly, my result is slightly different from my current model, as the current model's sales by geo depends on customer/geo associations. Due to some data quality issues, there might be sales identified by geo, but the customer is not correctly associated with geo. 


So whats a good way to solve it so I can associate sales with GEO/country directly, now it errors out as it forms a loop. 


Thank you so much!

Thejeswar Senior Member
Senior Member

Re: Data model question


You can remove the join between Customer and Geo and Join Geo/Country with Sales. In that means, you can achieve a direct join between Geo and Sales.


You don't really need a join between your geo table and Customer Table unless you have a hirearchy of Geo and Customer.


If you really need a join between Geo and Customer, then keeping the join between them untouched, create an inactive relationship between your Geo and Sales Table based on your key.


Then when ever you are using a combination of these tables, you should create column/measure using USERELATIONSHIP() DAX Function


There's also another way to solve it. Just Duplicate you Geo table, and join the new Geo table with your Sales table. So in report where Geo table drives, use from the one that is not connected to the Customer and in report where Customer table drives, use the Geo table joined with the customer


Hope this clarifies your question!!!

rajendran Super Contributor
Super Contributor

Re: Data model question



Since Your Geo table should be connected to both fact tables ,


1.  Remove the Join between Customer and Sales fact table 1

2. Copy Geo and Customer table using 'Reference' in Power Query and join Geo-> Customer-> Fact table 1.


So, in this case you will have the below joins:


Geo_copy -> Customer_Copy-> Facttable1

Geo -> Customer -> CRM Customer-> Facttable2


SInce you use 'Reference', Power BI will hit DB once and no performance issue as well.Hope this helps.




Helpful resources

Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 57 members 1,142 guests
Please welcome our newest community members: