Forum Discussion
Combining Column Data
Hi,
I am new to Power BI and have been given the task of creating dashboards for property types in my local area. The database being used has 4 levels for each property type but only some are required to be used to create dashboards and slicers, etc. Some of these need combining and data taking from different columns. An example of the 4 levels is below and the two columns titled Property Category and Property Type is what is required. There are over 100 different property types and each one has a personalised asset ID assigned to it.
| Level 1 | Level 2 | Level 3 | Level 4 | Property Category | Property Type |
| Building | Dwelling | House | Dwelling | House | |
| Building | Dwelling | Purpose built flat - multiple occupancy | 3 storeys | Dwelling | Purpose built flat - multiple occupancy - 3 storeys |
| Building | Non Residential | Offices and call centre | Converted office | Non Residential | Converted office |
| Building | Non Residential | Private garage | Non Residential | Private garage | |
| Road Vehicle | Car | Road Vehicle | Car | ||
| Other transport vehicle | Trains | Passenger train | Trains | Passenger train | |
| Outdoor | Grassland, woodland and crops | Standing crop | Outdoor | Grassland, woodland and crops - Standing crop | |
| Outdoor | Outdoor structures | Tunnel | Outdoor structures | Tunnel | |
| Outdoor | Outdoor equipment and machinery | Garden equipment | Outdoor equipment and machinery | Garden equipment |
I hope this is possible.
Hii PowerBIUser1990
You can create a calculated column to always return the deepest non-blank level using DAX:
Property Type = COALESCE( 'Table'[Level 4], 'Table'[Level 3], 'Table'[Level 2], 'Table'[Level 1] )For Property Category, if it is driven by Level 2:
Property Category = COALESCE('Table'[Level 2], 'Table'[Level 1])This ensures consistent classification and works correctly across all visuals and slicers.
7 Replies
Hii PowerBIUser1990
You can create a calculated column to always return the deepest non-blank level using DAX:
Property Type = COALESCE( 'Table'[Level 4], 'Table'[Level 3], 'Table'[Level 2], 'Table'[Level 1] )For Property Category, if it is driven by Level 2:
Property Category = COALESCE('Table'[Level 2], 'Table'[Level 1])This ensures consistent classification and works correctly across all visuals and slicers.
Hi rohit1991, this works really well for the Property Type but some of the Property Categorys come from Level 1 as well as Level 2
Hi PowerBIUser1990 ,
Can you please share the specific logic for the property type and property category?
Since you mentioned that the property type is working well with the COALESCE function, we can apply a similar approach for the property category.
For the property category, we should prioritize Level 1, then Level 2, as the COALESCE function relies on order and returns the first non-null value.
Based on the requirements, we can implement this logic. Is there a flag or identifier column that specifies which category level to use? Please share examples of your requirement along with some sample data.
thanks
If you found this helpful, please consider giving it a kudo and marking it as the accepted solution — it goes a long way in helping others facing the same issue.
For more Power BI tips and discussions, let’s connect on LinkedIn:
https://www.linkedin.com/in/natarajan-manivasagan
Cheers!
Hi Natarajan_M
The specific logic is based on the recording system we use for the asset IDs. There is a search facility on this system where you can search by 10 different Property Categories. These are as follows:
Dwelling
Other Residential
Non Residential
Road Vehicle
Trains
Aircraft
Boats
Outdoor structures
Outdoor equipment and machinery
Outdoor
I have been asked by my Manager to replicate these in the Power BI dashboards which is why the Property Category column is a mix of Level 1 and Level 2. I have provided this below:
Level 1
Level 2
Which column does Property Category need to come from
Building
Dwelling
Level 2
Building
Other Residential
Level 2
Building
Non Residential
Level 2
Road Vehicle
Car
Level 1
Other transport vehicle
Trains
Level 2
Other transport vehicle
Aircraft
Level 2
Other transport vehicle
Boats
Level 2
Outdoor
Outdoor structures
Level 2
Outdoor
Grassland, woodland and crops
Level 1
Outdoor
Outdoor equipment and machinery
Level 2
Outdoor
Other outdoors (including land)
Level 1
Thanks
Hi PowerBIUser1990 , Let me ask the question in a different way ?
How to identify which level to use for each row
For example
Case 1
if the level 1 is outdoor and level 2 is outdoor structures woodland and crops the required op is outdoor structures (Level 2)
Case 2
if the level 1 is outdoor and level 2 is grassland woodland and crops the required op is outdoor ( level 1)
How we know when to choose level1 and level2
on what basis ? whats the criteria ? if you can share this then we can resolve this issue
ThanksNot sure I can explain it in another way. Thanks for your help anyway I will just advise my Manager I cannot provide what he wants.
