Calculating level wise Asset Health percentage based on a hierarchical structure in Power BI

[Nludk_96]

Hi, I am working with a tree structured hierarchy in Power BI, and I need to calculate the minimum asset health percentage from its descendants at each level of the hierarchy. The hierarchy is defined using Asset ID and Asset Parent ID, which I use to create the relationships that form the tree structure. The challenge is that the hierarchy levels can vary depending on the dataset, and the table contains a large number of rows. I have an Asset Health table with the Asset ID, Asset Parent ID and Asset Health Percentage. 

Examples:

• If I select Asset ID = 1001, I need to calculate the minimum asset health value from its descendants : 1004, 1005, 1007, 1008, 1009.
• If I select Asset ID = 1004, I need to calculate the minimum asset health value from its descendants : 1007, 1008, 1009.
• If I select an Asset ID = 1009, there are no descendants, so the result would be no minimum value : BLANK

I would appreciate it if someone could help me to compute this dynamically. Any suggestions or guidance on how to handle hierarchies in Power BI Dekstop would be really helpful!

Thanks in advance!

[Nludk_96]

Hi @bhanu_gautam  and @v-kpoloju-msft 

Thank you so much for your help and suggestions on handling the scenario I was working on.I tried both of your suggestions, and @v-kpoloju-msft  your solution worked well for me. However, there was just one thing I noticed, it wasn't capturing the highest parent’s DescendantsList and DescendantsMinHealth value. As shown in the example I provided earlier:

• If I select Asset ID = 1000, I need to calculate the minimum asset health value from its descendants : 1001,1002,1003,1004, 1005, 1006,1007, 1008, 1009.

In this case, for Asset ID 1000, it’s not calculating DescendantsList and DescendantsMinHealth value correctly. 

To address this, I updated the DAX expressions used to create the calculated columns as follows and this now includes the descendants even for the highest-level parent.

DescendantsList=

VAR CurrentAsset = AssetHealth[Asset ID]
RETURN
CONCATENATEX(
FILTER(
AssetHealth,
(
LEFT(AssetHealth[Path], LEN(CurrentAsset)) = CurrentAsset
|| CONTAINSSTRING(AssetHealth[Path], "|" & CurrentAsset & "|")
|| RIGHT(AssetHealth[Path], LEN(CurrentAsset)) = CurrentAsset
|| CONTAINSSTRING(AssetHealth[Path], "|" & CurrentAsset)
|| CONTAINSSTRING(AssetHealth[Path], CurrentAsset & "|")
)
&& AssetHealth[Asset ID] <> CurrentAsset
),
AssetHealth[Asset ID],
","
)

DescendantsMinHealth =
VAR CurrentAsset = AssetHealth[Asset ID]
VAR DescendantTable =
FILTER(
AssetHealth,
(
LEFT(AssetHealth[Path], LEN(CurrentAsset)) = CurrentAsset
|| CONTAINSSTRING(AssetHealth[Path], "|" & CurrentAsset & "|")
|| RIGHT(AssetHealth[Path], LEN(CurrentAsset)) = CurrentAsset
|| CONTAINSSTRING(AssetHealth[Path], "|" & CurrentAsset)
|| CONTAINSSTRING(AssetHealth[Path], CurrentAsset & "|")
)
&& AssetHealth[Asset ID] <> CurrentAsset
)
RETURN
IF(
COUNTROWS(DescendantTable) > 0,
MINX(DescendantTable, AssetHealth[Asset Health])
)

This approach now correctly returns the DescendantsList and DescendantsMinHealth value for the top-level asset as well.

Thanks again for your input!

[v-kpoloju-msft · Accepted solution]

Hi @Nludk_96,
Thank you for reaching out to the Microsoft fabric community forum. Thank you @bhanu_gautam, for your inputs on this issue.

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.    

Output:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @Nludk_96,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[bhanu_gautam]

@Nludk_96 First, create a calculated column that represents the path from the root to each node. This will help in traversing the hierarchy.

Path = PATH(AssetHealth[Asset ID], AssetHealth[Asset Parent ID])

Next, create a measure that calculates the minimum health percentage of the descendants for the selected asset.

DAX
MinDescendantHealth =
VAR SelectedAsset = SELECTEDVALUE(AssetHealth[Asset ID])
VAR Descendants =
CALCULATETABLE(
AssetHealth,
PATHCONTAINS(AssetHealth[Path], SelectedAsset)
)
RETURN
MINX(Descendants, AssetHealth[Asset Health Percentage])

To handle the case where there are no descendants, you can modify the measure to return BLANK if the selected asset has no descendants.

DAX
MinDescendantHealth =
VAR SelectedAsset = SELECTEDVALUE(AssetHealth[Asset ID])
VAR Descendants =
CALCULATETABLE(
AssetHealth,
PATHCONTAINS(AssetHealth[Path], SelectedAsset)
)
VAR MinHealth = MINX(Descendants, AssetHealth[Asset Health Percentage])
RETURN
IF(
COUNTROWS(Descendants) = 1,
BLANK(),
MinHealth
)

Finally, use this measure in your Power BI report to dynamically calculate and display the minimum asset health percentage for the selected asset and its descendants.