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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Imrans123
Advocate V
Advocate V

How to address indirect relationship

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, 

Community Question.PNG

 

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?

1 ACCEPTED SOLUTION
sevenhills
Super User
Super User

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!

View solution in original post

4 REPLIES 4
sevenhills
Super User
Super User

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
Advocate V
Advocate V

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!

Issue.PNG

Customer Table 

Customer ID Customer Name Store ID Referred by 
1John1Google
2Jack 2Google
3Sally1Facebook 

 

Billings Table 

Invoice ID Customer ID Store ID Amount 
111100
211120
32250
42260
53150
63160

 

Store TAble 

Store ID Store Name 
1Store 1
2Store 2

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
Super User
Super User

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Top Solution Authors