Forum Discussion
Combining Column Data
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 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
Thanks
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.
Hi PowerBIUser1990 ,
Thank you Natarajan_M for the response provided!
Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.
Thank you.
