New table - expanding group level data in existing table

[m196804]

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

[lbendlin]

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?

[m196804]

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 

[lbendlin]

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.

[m196804]

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