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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
heavyarms
Frequent Visitor

Modeling relationships one to many in context

Hi Everyone,  Perhaps I'm over analyizing the problem with my model as I'm sure there must an efficient way of handling this scenerio.

 

The issue is that my fact table contains many columns that all relate back to the customer table (customer ID), the catch is that they are in different contexts.  For example in the fact table,  An order would have a  Ship_toCustomerID  that refers to a customer in the customer table, as well as a  BillToCustomerID that also refers to a customer in the customer table.   Power BI allows for a single primary key in the join along with surrogate keys, however If I try to present customer information for ShipTo and BillTo, I only get customer attributes back based on how the primary key has been set in the Power BI model.  I must be missing something.

 

Capture111.JPGCapture1112.JPG

 

 

 

 

1 ACCEPTED SOLUTION
heavyarms
Frequent Visitor

I would like to thank everyone who contributed to this topic.  Considering your excellent suggestions along with several hours of research,  My conclusion is this... 

 

In power BI we have three ways of addressing the issue of multible relationships between two tables,  the best solution being based on your specific situation and requirements.  Factors like.. amount of data & the need to show dimensions as text in the result rather than as part of a calculated measure. 

 

Each of these methods have been detailed earlier in this thread.

 

  • Pivot the fact to allow a single join to be established.
  • Using   "USERELATIONSHIP" function to feed join conditions as part of an expressive result
  • Breaking out the dimension into seperate tables (old school)

 

This topic was also explored in an articale from ExceleratorBI 

https://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax

 

 

I'm faily new to Power BI, but have deep experience in most other BI tools,  I'm dissapointed that Microsoft suffers from the same handicap as the rest.  If they could find a more elegant solution, that would certainly put Power BI in a class all by itself.

 

 

View solution in original post

10 REPLIES 10
heavyarms
Frequent Visitor

I would like to thank everyone who contributed to this topic.  Considering your excellent suggestions along with several hours of research,  My conclusion is this... 

 

In power BI we have three ways of addressing the issue of multible relationships between two tables,  the best solution being based on your specific situation and requirements.  Factors like.. amount of data & the need to show dimensions as text in the result rather than as part of a calculated measure. 

 

Each of these methods have been detailed earlier in this thread.

 

  • Pivot the fact to allow a single join to be established.
  • Using   "USERELATIONSHIP" function to feed join conditions as part of an expressive result
  • Breaking out the dimension into seperate tables (old school)

 

This topic was also explored in an articale from ExceleratorBI 

https://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax

 

 

I'm faily new to Power BI, but have deep experience in most other BI tools,  I'm dissapointed that Microsoft suffers from the same handicap as the rest.  If they could find a more elegant solution, that would certainly put Power BI in a class all by itself.

 

 

heavyarms
Frequent Visitor

Thanks,  This would be perfect if this was a calculation.  However the request that I have is to simply show the names of the customers side by side in a table view by order number.  for example BilltoCustomer & ShiptoCustomer names..   Most of the examples that I found are how to use USERELATIONSHIP with an expression like SUM.     I'm still learning DAX, so there is probably some simple expression for this that I have yet to find. 

I think what you need is only a relation between the two tables on customer_id. All the id's in your fact are for combining the data with multiple dimensions. So, if you want to combine the Sales Amount with attributes from the customer, you should join only by customer_id. And remove the other joins between these two tables.

 

For Example:

 

Table A (= fact):

customer_id

bill_to_customer_id

product_id

Sales ($)

 

Table B (= dimension):

customer_id

name

job

division

 

If you want to have the name (Table B) with the Sales amount (Table A) it is enough to join only on the customer_id.

 

They call this "Dimensional Modelling".

The issue that I'm trying to solve has to do with contextual reference.  If for an order you have a Customer and a BillToCustomer (that are not the same) and join to a customer table in a single contexted join (i.e. FactCustomerID to DimCustomerID). Your going to return the value based on the nature of the context referenced in the join.  In other BI tools, they solve this by breaking the dimension out into several tables to support each context.  In the data  world we would just form a unique key, however Power BI seems to approach this in a different way.  The use of USERELATIONSHIP will solve for a measure, but I'm just trying to present the data in a table as is.  USERELATIONSHIP requires an expression to work. so  I just need to find an expression that presents the data without doing any sort of summation.


@heavyarmswrote:

 In other BI tools, they solve this by breaking the dimension out into several tables to support each context. 


You can do this in the Query Editor. Reference (or duplicate) your table from source as many times as you have contexts and then filter each new table and create your keys in the fact table.

That method of modeling is very inefficient, so I would like to avoid it if at all possible.  There must be a better answer than to just create 6 different customer tables just to show for any Order a BillToCustomer next to a SoldToCustomer.

@heavyarms,

You can unpivot your columns in the sales fact table, then create relationship between the sales table and customer table. For more details, please review the following screenshots.

1.JPG2.JPG3.JPG

Regards,
Lydia

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

Wouldnt that require that I define the context in DAX everytime I need to visualize data that is outside of the primary key?  I was hoping for something that could be applied in the model to acheive this.

Build a few measures, try it out. 

phepburn
Frequent Visitor

What luck have you had with USERELATIONSHIP() https://msdn.microsoft.com/en-us/library/hh230952.aspx ?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.