I’ve created an org chart using Davide Bacci’s template. The chart works as expected, but I want to add status monitoring logic and I’m having trouble with it. By default, the Tier 3 drivers have a status which is used to determine the statuses of the other tiers. Each tier and its respective owner are represented in the data model for accountability and tracking purposes. Attached are an data, image and the PBIX file showing what I’m trying to achieve.
Link for the Pbix file
What I Expected: I expected that:
-
If any Tier 3 driver is “Off track,” the associated Tier 2 driver and its owner would be “Off track.”
-
If no Tier 3 drivers are “Off track” but at least one is “At risk,” the associated Tier 2 driver and its owner would be “At risk.”
-
If all Tier 3 drivers are “On track,” the associated Tier 2 driver and its owner would be “On track.”
-
Similar logic for updating the statuses of Tier 1 outcomes, objectives, and their respective owners.
What Actually Happened:
The logic did not work as expected. The statuses of the Tier 2 drivers, Tier 1 outcomes, objectives, and their respective owners did not update correctly based on the rules I set up.
Status Monitoring Rules:
-
Tier 2 Driver Status:
-
“Off track” if any associated Tier 3 Driver is “Off track.”
-
“At risk” if no Tier 3 Drivers are “Off track” but at least one is “At risk.”
-
“On track” if all associated Tier 3 Drivers are “On track.”
-
-
Tier 1 Outcome Status:
-
“Off track” if any associated Tier 2 Driver is “Off track.”
-
“At risk” if no Tier 2 Drivers are “Off track” but at least one is “At risk.”
-
“On track” if all associated Tier 2 Drivers are “On track.”
-
-
Objective Status:
-
“Off track” if any associated Tier 1 Outcome is “Off track.”
-
“At risk” if no Tier 1 Outcomes are “Off track” but at least one is “At risk.”
-
“On track” if all associated Tier 1 Outcomes are “On track.”
-