Forum Discussion
Help Needed with Power BI Data Modeling
Hi DanieleR , to overcome this, you need to do some modeling.
At first, let's rebuild SalaryData with these columns
Town_Code GenderKey CategoryKey Salary abcde 2 1 19.1 abcde 2 2 13.2 abcde 2 4 9.7 abcde 1 6 18.6 Now, we need to create a bridge table that handles both job type and age group
DimCategory
CategoryKey CategoryType CategoryName SortOrder 1 JobType Executives 1 2 JobType Mid_Managers 2 3 JobType Employees 3 4 AgeGroup 18to25 10 5 AgeGroup 26to49 11 6 AgeGroup 50plus 12
We have location informations so create another table for thatDimTown
Town_Code TownName Department Region abcde Paris Paris Île-de-France fghij Lyon Rhône Auvergne-Rhône klmno Marseille Bouches-du-Rhône Provence-Alpes-Côte
and To get the gender Create another table
DimGender
GenderKey Gender 1 Male 2 Female
And, finally create this relationship with your other old tables
SalaryData → DimCategory (CategoryKey)
SalaryData → DimTown (Town_Code)
SalaryData → DimGender (Sexe)
Population → DimTown (Town_Code)
Enterprises → DimTown (Town_Code)
Now, you have an organized model to answer your all queries.
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
The example tables you have provide are "reports" withj totals.
They contaion no underlying details to build relationships.
If you have access to underlying data then consider dumping it into one simple table with one record per person.
- PersonID
- Gender
- Date Of Birth
- Age (calculated column using DOB)
- Enterprise / Company name|
- Region
- Town
- Department
- Commune
Answers to youy questions:
- Use Age Banding tables to report age bands. Ask if you need more help with age bands.
- Start schema is not needed for this simple model.
- Simple count the number of People by Country, Company, Region, Town, Job Type, Age Band or whatever.
- None are needed for this simple model.
- It is best practice to use geographic data types. Learn about geographic data types here:-https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-mobile-geofilteringhttps://learn.microsoft.com/en-us/power-bi/visuals/power-bi-map-tips-and-trickshttps://www.youtube.com/watch?v=LFMn3wnruNcPlease click thumbs up because I have tried to help.
Then click [accept solution] if it works.
Thank you