Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have been searching for a solution for this but I'm struggling to phrase what I am trying to do in order to find anything helpful.
I have a table which has data at 3 levels. Data can be attibuted to level 1, 2 or 3. I want to create a table for all level 3 records which also incorporates the data at levels 1 and 2 depending which groups they belong to. An example using Countries, Regions and Towns, which hopefully makes it clearer, is below.
In this scenario, I would like is for each town (level 3) to be populated based on the data available for the country (level 1) or region (level 2) they are in.
So for example becase Birmingham is in both the West Midlands region and the UK country, it should have a new row, for each of the region data rows and for each of the country data rows.
Current data
Data Level | Country | Region | Town | Data | Date |
Country | UK | CountryData1 | 01-Jan-24 | ||
Country | UK | CountryData2 | 04-May-24 | ||
Region | UK | West Midlands | RegionData1 | 03-Jun-22 | |
Region | UK | South West | RegionData2 | 20-Aug-22 | |
Town | UK | West Midlands | Birmingham | TownData1 | 31-Jan-24 |
Town | UK | South West | Poole | TownData2 | 30-Sep-21 |
Expected Output
Data Level | Country | Region | Town | Data | Date |
Country | UK | West Midlands | Birmingham | CountryData1 | 01-Jan-24 |
Country | UK | West Midlands | Birmingham | CountryData2 | 04-May-24 |
Country | UK | South West | Poole | CountryData1 | 01-Jan-24 |
Country | UK | South West | Poole | CountryData2 | 04-May-24 |
Region | UK | West Midlands | Birmingham | RegionData1 | 03-Jun-22 |
Region | UK | West Midlands | Birmingham | RegionData2 | 20-Aug-22 |
Region | UK | South West | Poole | RegionData1 | 03-Jun-22 |
Region | UK | South West | Poole | RegionData2 | 20-Aug-22 |
Town | UK | West Midlands | Birmingham | TownData1 | 31-Jan-24 |
Town | UK | South West | Poole | TownData2 | 30-Sep-21 |
How can I achieve this in DAX please? Thank you
Is the town data rolling up to the region and the region data rolling up to the country? Or do region and country also have their own data points?
Thank you for replying.
Region and Country have their own data points. For each country's data point I want to create new rows for all regions that belong to each country with the country's data point assigned to each region. For towns I want to create new rows for all the towns that belong to each region with the associated region's datapoints and new rows for the country each town belongs to with the associated county's data points.
I hope that makes sense, it's tricky to explain
The standard approach in such a scenario is to leave these hierachy levels blank, like in your original table. Your expected output is not a desirable outcome, it creates more problems than it solves.
Maybe I am not explaining this properly.
I am using countries, regions, etc which I know is a contrived example as I can't publish my actual data, but it is an equivalent data hierarchy:
If I leave the table as it is, it doesn't give me the information I need for each town. I need to generate a table showing the country and region level data points associated with each town.
For example if there is a country level row as follows:
Level, Country Region Town, Flag
Country, UK, null, null, Union Jack
From that information I need to create records for all the towns with their flag which in this case will also be the Union Jack as they are in the UK.
Level, Country, Region, Town, Flag
Country, UK, West Midlands, Birmingham, Union Jack
Country, UK, Dorset, Poole, Union Jack
I have a separate table which shows the countries, regions, towns hierarchy
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |