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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
m196804
Frequent Visitor

New table - expanding group level data in existing table

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 LevelCountryRegionTownDataDate
CountryUK  CountryData101-Jan-24
CountryUK  CountryData204-May-24
RegionUKWest Midlands RegionData103-Jun-22
RegionUKSouth West RegionData220-Aug-22
TownUKWest MidlandsBirminghamTownData131-Jan-24
TownUKSouth WestPooleTownData230-Sep-21

 

Expected Output

Data LevelCountryRegionTownDataDate
CountryUKWest MidlandsBirminghamCountryData101-Jan-24
CountryUKWest MidlandsBirminghamCountryData204-May-24
CountryUKSouth WestPooleCountryData101-Jan-24 
CountryUKSouth WestPooleCountryData204-May-24
RegionUKWest MidlandsBirminghamRegionData103-Jun-22
RegionUKWest MidlandsBirminghamRegionData220-Aug-22
RegionUKSouth WestPooleRegionData103-Jun-22
RegionUKSouth WestPooleRegionData220-Aug-22
TownUKWest MidlandsBirminghamTownData131-Jan-24
TownUKSouth WestPooleTownData230-Sep-21

 

How can I achieve this in DAX please? Thank you

4 REPLIES 4
lbendlin
Super User
Super User

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

 

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.