2 fact table fields linking to 1 reference table

[jim2004]

Hello all,

I am very new to BI so please excuse my ignorance.

I have a fact sales table that has a billto customer number and a shipto customer number. Sometimes these fields are the same and sometimes they are not. I need to link them both to the customer master. I am wondering the best way to accomplish this. It would seem I could just duplicate the customer master and have a shipto customer master and a billto customer master. That seems very inefficient from a data perspective though. Is there a better way to handle this?

[ebeery]

Hi @jim2004 ,  what types of measures and visuals do you anticipate wanting to build off of these two different fields? 

The method you described of having two dimension tables is one approach, but another would be to stick with a single customer master and create a 2nd "in-active" relationship with the fact table.  Then, by using the USERELATIONSHIP you can toggle each relationship on and off within a measure as needed.  There are pros and cons to both approaches, but which to use would depend on what you're trying to accomplish.

https://www.sqlbi.com/articles/using-userelationship-in-dax/

[jim2004]

It's really just for reference. I'd be pulling things like name, address zip, ect. I am not familiar with how to mange the inactivate relationship but I will explore this further. Thank you for the help.

[Anonymous · Accepted solution]

Hey, 

Sounds like a easy fix, check out this video on creating relationships - 

https://www.youtube.com/watch?v=8ybsChtuZoY

[jim2004]

Thank you. I watched that video and a few others on duplicate versus reference. What I found interesting was when you create a reference,  the query still pulls the original data again to build that reference. I would have thought it would have pulled it's data from  the original query and not gone back to the source again, So if I understand correctly weather I use reference of duplicate the customer master source will be read twice. This will work just fine I just wanted to be sure I was not missing a better way to accomplish the task.

FYI here is the video I watched that did a good job of what is really happening behind the scenes when you use reference.

https://www.youtube.com/watch?v=3uKNNZqBIkg

Thanks again for the help