Gauge target value based on hierarchy slicer

[AlexSA]

hello

• I have a Gauge visual that is used to show % of task completed. Value from data set 1
• I have a hierarchy slicer that allow selection of a category from 4 levels. This is fed also by data set 1
• I have separate sets of data for each category level with respective goals (Data sets: 2, 3, 4 and 5), as we have a significant number of categories under the different levels, we have selected critical ones, which account for approx. 10% of the number of categories, thus the category goals don’t aggregate by level, these are independent values.

I would like that Gauge Target value to dynamically change depending on which category is selected in the hierarchy slicer (considering category can be selected from 4 different levels).

I considered 3 scenarios,

Partial solution: use a single level slicer, this will allow the gauge to find the target value from the goal table (relationship exists). But unfortunately, it is not a solution cause I need to give the option to dig down to lower levels.

Work around solution: create a single data set (data set 6) with a column for the category name and a column for the goal (including all category levels in the same column, fortunately there are no name duplicates). Thus I need to find a way to create a measure that would allow to automatically find the target value in the new table that has no relationship with data set 1.

Ideal solution: create a measure that will be used as target value in the gauge, which will search the selected category (hierarchy slicer) and find the respective goal in the respective goal data set (data sets 2, 3, 4, 5)

your advice will be greatly appreciated

[AlexSA · Accepted solution]

hello, thank you for the suggestion, I got a partial solution, not perfect, but close enough (for now)

• Unpivot the levels, which created a single row of data for each level, meaning it can be counted under Level 1, 2, 3 and 4 separately
• Then I created a data set with the goals in a single column for the level names and a column for the goals
• Used a filter with the attribute and value created when I unpivoted the original data set
• Then I made a relationship among the two data sets, i created a table and this works for the filter but still didn't work for the gauge, the target value was not being pulled, so I used the suggested formula to create a measure, with a lookup value to find the goal value for the selecterd filters, and then used the measure in the gauge.

Thanks!

[Anonymous]

Hi @AlexSA ,

It is recommended that you try the Field Parameters feature, please refer to

Let report readers use field parameters to change visuals (preview) - Power BI | Microsoft Learn

Field parameters allow users to dynamically change the measures or dimensions being analyzed within a report. This feature can help your report readers explore and customize the analysis of the report by selecting the different measures or dimensions they're interested in.

If the field parameter does not work, you may also try creating a  Unified Goals Table and a measure that will dynamically find the target value based on the selected category from the hierarchy slicer. Below are the detailed steps for the approach:

Step 1: Create a Unified Goals Table
First, you should consolidate your separate goal datasets (Datasets 2, 3, 4, and 5) into a single dataset (Dataset 6) that contains all the goals for each category level. This unified table should have at least two columns: one for the category name (ensuring there are no duplicates as you mentioned) and one for the goal value.

Step 2: Use DAX to Create a Dynamic Measure
Next, create a DAX measure that will dynamically find the target value based on the selected category from the hierarchy slicer. You can use the LOOKUPVALUE function to search for the goal in the unified goals table. Here's an example of how you might write this measure:

DynamicTargetValue =
VAR SelectedCategory = SELECTEDVALUE('DataSet1'[CategoryColumnName])
RETURN
LOOKUPVALUE(
'DataSet6'[GoalValue],
'DataSet6'[CategoryName], SelectedCategory
)

Replace 'DataSet1'[CategoryColumnName] with the actual column name from your primary dataset that corresponds to the category selected in the slicer, and adjust 'DataSet6'[GoalValue] and 'DataSet6'[CategoryName] to match the column names in your unified goals table.

Step 3: Apply the Measure to the Gauge Visual

Hope that helps.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.