Forum Discussion

Negi's avatar
Negi
Helper I
2 years ago

Need advice how to create data model where multiple columns combination

Hi All,

I am new to Power BI, Can anybody suggest me how to create data model for my specific scenario.

I have below mentioned tables :-

1) Resource Plan : it contains details for all resources daily how much hours they have to fill domain wise.

2) Timesheet : it contains the data for all resources which contains details how much hours resources fill in each domain day wise.

3) Cji3 : it contains the data of resources which contains the details how much cost posted for each resources day wise.

Now I want to create a matrix table view where I want find below mentioned answers :-

  • I want to check Resource wise table view where I can check how much planned hours vs actual hours(from timesheet table) resources fill based on Domain tagged in Resource plan as well as booked to others domains where they are not tagged.
  • I want to check Resource wise table view where I can check how much planned Cost vs actual Cost(from Cji3 table) resources fill based on Domain tagged in Resource plan as well as booked to other domain where they are not tagged.

Can any body suggest , me how to create the data model or I need to build a logic in Power Query to combine all the data in one table ?

or can anybody suggest me or provide the Schema how to build relationship between all the above mentioned tables ?

Thanks a lot for your valuable feedback and sharing any useful video link for same type of query  or share the snapshot of data model created based on sample data attached ?

 

Power BI Query Data Modelling - Copy.xlsx

Need Help
Tips and Tricks

1 Reply

  • GrowthNatives's avatar
    GrowthNatives
    Super User
    1 year ago

    Hi Negi , 

    Thanks for the detail description of you issue, its a common problem that we face while doing data analysis in power bi, here is a step by step approach that will help you to solve your problem. 😊

     

    Based on what you’ve described, here’s a structured plan to help you model this in Power BI without merging everything into a single table, which would reduce flexibility. Instead, we’ll go with a star-schema-style approach with proper relationships.

    🔍 Objective Summary:

    You want to analyze:

    • Planned vs. Actual Hours (by Resource + Domain)

    • Planned vs. Actual Cost (by Resource + Domain)

    • Also show what was booked outside of planned domains

    🧩 Your Source Tables:

    1. Resource Plan

      • Columns: Resource, Date, Domain, Planned Hours

    2. Timesheet

      • Columns: Resource, Date, Domain, Actual Hours

    3. CJI3

      • Columns: Resource, Date, Domain (if available), Actual Cost

    ✅ Recommended Data Model Structure

    Step 1: Create Dimension Tables

    Create the following dimension tables to centralize lookups:

    • Dim_Resource (distinct Resource)

    • Dim_Domain (distinct Domain)

    • Dim_Date (calendar table with continuous dates)

    You can create these using DAX or Power Query:

     

    DAX:
    Dim_Resource = DISTINCT(UNION(SELECTCOLUMNS(ResourcePlan, "Resource", ResourcePlan[Resource]), SELECTCOLUMNS(Timesheet, "Resource", Timesheet[Resource]), SELECTCOLUMNS(CJI3, "Resource", CJI3[Resource]))) Dim_Domain = DISTINCT(UNION(SELECTCOLUMNS(ResourcePlan, "Domain", ResourcePlan[Domain]), SELECTCOLUMNS(Timesheet, "Domain", Timesheet[Domain])))

    Step 2: Build Relationships

    Now connect your fact tables to dimensions:

    Table Join on Relationship Type
    ResourcePlanResource → Dim_Resource[Resource] 
     Domain → Dim_Domain[Domain] 
     Date → Dim_Date[Date] 
    TimesheetResource → Dim_Resource[Resource] 
     Domain → Dim_Domain[Domain] 
     Date → Dim_Date[Date] 
    CJI3Resource → Dim_Resource[Resource] 
     Date → Dim_Date[Date] 
     Domain → Dim_Domain[Domain] (if available) 

    Make all these many-to-one single-direction relationships.

    🧠 Key Calculated Measures

    Then create DAX measures like:

     

    DAX:
    Planned Hours = SUM(ResourcePlan[Planned Hours]) Actual Hours = SUM(Timesheet[Actual Hours]) Actual Cost = SUM(CJI3[Actual Cost])

    To check “outside of planned domain” entries:

     

    DAX:
    Hours Outside Planned Domain = CALCULATE( [Actual Hours], EXCEPT( VALUES(Timesheet[Domain]), VALUES(ResourcePlan[Domain]) ) )

    📊 Matrix Visual

    Add a matrix with:

    • Rows: Resource, Domain

    • Columns: Date or Month

    • Values: Planned Hours, Actual Hours, Actual Cost, etc.

    💡 Pro Tips:

    • Don’t combine all tables into one—it’s best to normalize and use dimension tables.

    • If domain info is missing in CJI3, consider inferring it based on Timesheet data if available.

    • Make sure all dates align properly via a common Dim_Date.

      ⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
      💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
      🚀Let’s keep building smarter, data-driven solutions together! 🚀