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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
powerbi92614
New Member

Help with building a project (creating relationships)

I manage customer escalations for my company and the process is 100% manual. Currently, my co-worker and I use three google sheets to log each escalation we receive.

  • The first google sheet is titled “2019 Customer Escalations” and there is a tab for each month. This is where we log each customer escalation.
  • The second google sheet is titled “2019 Store Details” and it’s also split up into monthly tabs that cover all our store’s information for that given month. The information for these store change monthly which is why we can’t use one tab for the entire year.
  • The third google sheet is titled “Manager Detail Report”. It goes over the information for each store manager and how many stores they are managing for that given month. Unfortunately, this information also changes frequently which is why we update it monthly.

 

I have converted all of these google sheets into excel documents and loaded them into Power BI. The issue I’m having is creating a relationship between all these documents. Here are the documents I have so far:

 

  1. January Manager Detail Report
  2. January Store Details
  3. January Customer Escalations
  4. February Manager Detail Report
  5. February Store Details
  6. February Customer Escalations
  7. March Manager Detail Report
  8. March Store Details
  9. March Customer Escalations
  10. April Manager Detail Report
  11. April Store Details
  12. April Customer Escalations

 

What I would like to do is have one Power BI report that I can send to an end user once a month that will show how our escalations are doing. I would like the report to have about 14 tabs by the end of the year. I’m envisioning a tab that collects all the data we have so far and outlines the annual analytics. This tab would show you at a glance a numerical value of many escalation each store received for a given month, it would also have a bar graph so you can see what the growth looks like, etc.

 

The second tab would outline all the managers and how many stores they were managing each month. I’m visualizing being able to click on a manager’s name and then having a table that shows their count and how it has changed each month.

 

The remaining tabs would be monthly tabs that break down the escalations. Each monthly tab will show you the details of the escalations for that given month. You’d be able to see the category for each escalation, which manager received the most escalations, which store received the most escalation, etc.

 

I’m stuck on how to best build this. I’m new to Power BI and have taken the Microsoft training and plan on taking more training; however, I learn best while diving in.

 

Currently, I only have 6 excel documents loaded in Power Bi. Basically, the first 6 in the above break down. I am trying to create a relationship between each report; however, I’m now getting an error that reads “You can’t create a direct active relationship between February Store Details and 4February Manager Detail Report because that would introduce ambiguity between tables”.

 

Any guidance would be greatly appreciated!

1 REPLY 1
MFelix
Super User
Super User

Hi @powerbi92614,

There are several things that you should do but first of all you should append all your similar tales into 1 table so you would only get 1 table of each:
Manager details
Store detaisl
Escalation

Taking into account that you have the information by month you should also add a date column to your tables with beginning or ending month date and a calendar table to relate the 3 tables.

Another dimension tables like store, manage or similar probably would also fit your needs, creating a star shape model.

Without additional information is difficult to give more than this.

Can you share a sample or mockup data?

Regards,
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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