How to address indirect relationship

[Imrans123]

Hello, 

Was wondereing if anyone could help me. Let's say we have a company that has multiple stores and each of these stores sells items and has billings data. There is a store table that would define the name of each store.

If a customer comes into the store, then a new entry is made in the Customer table and a new customer ID is issued from that particular store. The customer is also asked from where he/she was referred from for marketing purposes. Once registered, he/she can buy items from that store 

Everytime a customer buys something, a invoice is recorded onto the billings table. 

The tabel structure is as follows, 

The issue here is that I cannot create a relationship between store ID and Customer ID. Suppose in a visual, I would like to see, for each store what the count of each referred by stream was. How could i go on about doing this?

[sevenhills · Accepted solution]

Like @lbendlin replied, In my view, Billings and Store has to be active relationship.

My 2 cents: Additional info: for understanding

Typically, what you are showing is that Customer-to-store relationship as more of Application behavior. Say, you are generating a bill, and you make sure customer is part of the store i.e., customer + store combinations are valid. or associating a customer to a particular store as their preference. OLTP model.

For visualizations, we use the transactional data, i.e., your Billings table data. 

customer - store relationships - either remove or keep as inactive. I prefer to be removed.

Imagine scenarios for the modeling used for Power BI:

A scenario that a customer is associated with a store, but never made any sales/billing. Since he was enrolled, he got assigned or due to bad data. Another scenario, where he has data only for one period/year but not for other with that particular store, based on customer-to-store relationship. Do we need this for the visualization? No. It is always good to have relationships and do visualization with transaction data.

Once you have the relationships correct, i.e., data model is right, your table/chart visualizations automatically shows correct.

I added additional info, more for understanding.

Hope this helps!

[Imrans123]

Hi lbendlin, 

Could you please clarify how I could do this with DAX Measure? More specifically, I have a page which is showing me two visuals.

1. A matrix where I have Store Name (From Store Table) and values I have Amount from Billings Table.

2. A bar chart where I am showing the referred by. Axis has Referred by Column from Customer table and Values have Count of referred by from Customer Table. 

In addition, I have a slicer where Value is Store Name from Store Table. 

Now, If I were to filter Store 1, My Matrix changes, but my bar chart stays the same. Any idea how I could make the bar chart interact with the slicer?? 

THank you!

 

Customer Table 

Customer ID	Customer Name	Store ID	Referred by
1	John	1	Google
2	Jack	2	Google
3	Sally	1	Facebook

Billings Table 

Invoice ID	Customer ID	Store ID	Amount
1	1	1	100
2	1	1	120
3	2	2	50
4	2	2	60
5	3	1	50
6	3	1	60

Store TAble 

Store ID	Store Name
1	Store 1
2	Store 2

[lbendlin]

That is expected behavior as the referrals are not an attribute of stores (unless I misunderstand your business process).  In fact, I have removed the Store ID from your Customers table as it is not a customer attribute.

See attached for my proposal of your data model. 

[lbendlin]

You don't need the link between Customers and Stores.  These are independent dimensions that both control the fact table (Billings).  Remove the Customers - Store link and activate the Stores - Billings link.

Everything else can be done through measures.