Combining Column Data

[PowerBIUser1990]

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.

[PowerBIUser1990]

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

[Natarajan_M]

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 

Thanks 

[PowerBIUser1990]

Not 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.

[Natarajan_M]

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!
 

[rohit1991]

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.

[PowerBIUser1990]

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