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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ldrysdale13
Regular Visitor

Data modeling and report structure

Hi, 

 

I am trying to create a visual similar to the picture below using the data in my spreadsheet. This report will be used for scheduling purposes to show workload by employee. The desired report format shows by employee (and by project), the employees total estimated time scheduled for each category (there are 6 different categories). For each category, there is an Employee Name column and an Hours column. An employee could fall within one category or multiple categories. I have attached a sample data set. Ultimately I would like to be able to see by employee, the total hours for each category for each project. All my data is in one spreadsheet and am unsure how to structure the data and relationships within power bi to achieve this.

Desired Visual

Desired ReportDesired Report

 

 

 

Data sample

Sample Data.png

4 REPLIES 4
amitchandak
Super User
Super User

@ldrysdale13 , where is the category column in your data?

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

There are 6 Category columns show in in the Sample Data image above - Horizontal Lead/PM, Design, Plan Production, Stormwater/Support, Landscape Architecture, LA support

@ldrysdale13 , if you want to deal with these 5 using role-playing dimension then refer to this - https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

 

You can create a common table like

distinct(union(distinct(table[Horizontal Lead/PM]), distinct(table[Design]), distinct(table[Plan Production]), distinct(table[Stormwater/Support]), distinct(table[Landscape Architecture]), distinct(table[LA support])))

 

Star schema is best. You can take out few dimension from this table -  https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

 

In this series, in the first few videos( 2 and 3rd), I have broken a single table into the star

https://www.youtube.com/watch?v=kU2M1LmNvNo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=2

 

Thank you for your response. I tried to use the role-playing dimension and created separate tables from my original table for each category. I also pulled in an Employee fact table that has a relationship to each category table through Employee Name. This seems to work OK from an overall employee/category standpoint, but I am struggling on how to also incorporate the Project related information. I also created a project fact table from my original table but struggling on how to also get the correct project data (specifically project number) into my visuals. 

 

ldrysdale13_0-1620642682472.png

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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