cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cedberg
Frequent Visitor

Visual Totals Not Updating Properly Due to Relationships Between Fact and Dimension Tables

I am in the process of creating historical/point in time performance reporting for my call center.  In the business, it is not usual for an employee to report to different supervisors throughout the year.  I have created and update weekly an ongoing list of employees and the supervisor they report to, essentially creating a fact table.

 

I have created a relationship with this employee table with a calendar table in order to determine who an employee reported to at any given time of the year.  The point is to be able to rollup employee data up to the supervisor and be able to track and trend  supervisor performance throughout the year.

 

I have been able to create the relationships to show the proper employee under the proper supervisor at any point in time but my issues start when I try to connect this employee table with additional fact tables (performance data).  I am able to show the proper performance metrics to the individual employee but I am not able to get the data to properly roll up to the supervisor level.

 

I know the issue is because I am trying to pull data from two fact tables without the proper dimension tables.  However, to create the dimension table with the correct employee to supervisor hierarchy, that data would need to come from the employee table which is a fact table.

 

Any ideas and help on how to have the data properly roll up to the supervisor level will be greatly appreciated!

 

 

Table with employee data not correctly rolling up to Supervisor:

 

Visual.JPG

 

 

 

Schema.JPG

1 ACCEPTED SOLUTION
Cedberg
Frequent Visitor

I was able to solve the issue by adding some additional information during the query process that allowed me to properly roll up the data by the correct supervisor for any point in time.

View solution in original post

2 REPLIES 2
Cedberg
Frequent Visitor

I was able to solve the issue by adding some additional information during the query process that allowed me to properly roll up the data by the correct supervisor for any point in time.

View solution in original post

v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Cedberg,

 

I suggest you could change the direction highlight in red to both then try again.

 

Untitled.png

 

If you still need help, please share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

 

I would appreciate it if you could share you desired output.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.