Goals Reporting - Linking a Goal to a Subgoal (or a Sub-Sub-Goal...)

[Anonymous]

I've just started working with Goals and have a requirement to summarise some of the goal progress in a report for ease of use.

Within the goals framework I have two overarching goals.  One of them has six sub-sections (subgoals) and within those there are a number of subsequent subgoals (and sometimes a further subgoal under that).  So typically it looks like this:

• Main Goal (Effectively a "master heading" goal).  E.g. Implement BI Goals by 2023 (ID 1, for example)
  • Subgoal under this.  E.g. Prepare key objectives (ID 1-1, Parent ID 1)
    • Subgoal under this.  E.g. Identify 2022 milestones (ID 1-1-1, Parent ID 1-1)
      • (Possibly) subgoal under this.  E.g. Send user requirements by end of Q2 (ID1-1-1-1, Parent ID 1-1-1 etc)

My end user is looking to see at a glance which "major" goals aren't yet completed (example in red), and their associated notes.  So this means using a filter - probably a slicer - to identify the main goal, I've done that and it looks fine.

What I need to do next though is create an associated matrix of subgoals under that parent ID with their status/notes etc.  This is where I'm stuck - I'm unsure how to tell the matrix to reference the parent ID of the item selected in the slicer, and thus return the associated entries.  Since Goals is quite tightly locked down I can access the PBIX file but I can't edit relationships or see the data tables (everything is connected to the dataset, but nothing is held locally), which leaves me largely relying on DAX.

Has anyone managed to do anything similar with it yet?

(Largely useless) example file: https://www.dropbox.com/s/2remz46phv6d0c6/Example%20Scorecard.pbix?dl=0

[technolog]

Step 1. Create a tiny disconnected table for the slicer with GoalID and Name. If you cannot author a calculated table in this file, drop in a two column Excel sheet and load it. The columns must mirror the dataset values for GoalID and Name.

Example calculated table if you can add one

Slicer Goals =
DISTINCT (
SELECTCOLUMNS (
Goals,
"GoalID", Goals[GoalID],
"Name", Goals[Name]
)
)

Put Slicer Goals[Name] in a slicer. Turn on single select.

Step 2. Capture the selected goal

Selected Goal ID =
SELECTEDVALUE ( 'Slicer Goals'[GoalID] )

Step 3. Tell the matrix to only show children of that selected goal

Show Row Children =
VAR selected = [Selected Goal ID]
VAR parentThisRow = SELECTEDVALUE ( Goals[ParentID] )
RETURN
IF ( NOT ISBLANK ( selected ) && parentThisRow = selected, 1 )

Place Goals[Name] on Matrix rows. Add Show Row Children to the visual level filters and keep only rows where the measure equals 1. For values you can expose per row fields through simple wrapper measures like

Status =
SELECTEDVALUE ( Goals[Status] )

Notes =
SELECTEDVALUE ( Goals[Notes] )

That gives you a slicer for the main goal, and a matrix that lists all immediate subgoals with their status and notes. The selected parent goal itself will not appear because its Parent ID is different from its own ID.

Multi select variant for the slicer if you need it

Show Row Children Multi =
VAR chosen = VALUES ( 'Slicer Goals'[GoalID] )
VAR parentThisRow = SELECTEDVALUE ( Goals[ParentID] )
RETURN
IF ( CONTAINS ( chosen, 'Slicer Goals'[GoalID], parentThisRow ), 1 )

Deeper than one level
If you want children, grandchildren, and so on, add a parent child path column. This requires a calculated column

Path =
PATH ( Goals[GoalID], Goals[ParentID] )

Now filter by whether the current row is in the path of the selected parent

Show Row Descendants =
VAR selected = [Selected Goal ID]
VAR p = SELECTEDVALUE ( Goals[Path] )
VAR thisId = SELECTEDVALUE ( Goals[GoalID] )
RETURN
IF (
NOT ISBLANK ( selected )
&& PATHCONTAINS ( p, selected )
&& thisId <> selected,
1
)

[Anonymous]

Here's how it looks in the report screen with just the basic data:

Let's say I want to see what's outstanding for "Identify 2022 Milestones".  I would create a slicer for Name and filter down to that goal (Parent ID 74baf..._).  What I'd like to be able to do then is show the goals beneath that (i.e. which belong to Parent ID 74baf... - such as Prepare Key Objectives) in a matrix format.

[v-yanjiang-msft]

Hi @Anonymous ,

Unfortunately I have no access to your dataset.

Could you please provide more details about your sample? You can also show it in text. Sorry I'm not very clear about your struggled problem.

Best Regards,
Community Support Team _ kalyj