How to use Top N visual filter with Field parameter?

[bengtsson_peter]

I have two field parameters:

Parameter1 =
{
("Year", NAMEOF('Dates'[Year]), 0),
("Month", NAMEOF('Dates'[Month]), 1),
("Week", NAMEOF('Dates'[Week]), 2)
}

Parameter2 = {
("Activity", NAMEOF('MyTable'[Activity]), 0),
("Type", NAMEOF('MyTable'[Type]), 1),
("Person", NAMEOF('MyTable'[Person]), 2)
}

I have a Stacked column chart:
X-axis: 'Parameter1'[Parameter]
Y-axis: Sum of 'MyTable'[TimeSpent]
Legend: 'Parameter2'[Parameter]

I have two slicers - one for each parameter.

So far, so good - the chart behaves exactly as I want up to this point.

But now I want to filter the chart to the Top 8 TimeSpent sums of whichever item I select in the slicer for Parameter2 (Activity/Type/Person). But I can't figure out how to do that.

If I add 'Parameter2'[Parameter] to the chart's filter, select Top N, add 'MyTable'[TimeSpent] to "By value" nothing happens at all when applying the filter.

Any ideas of how to achive this?

(I am not interested in how to dynamically change the "N", which is the only thing I've found when googling on this.)

[PowerBiKing]

Old thread but I had a similar issue and want to share the solution with others who may be searching.

I have a field parameter linked to a slicer that changes the measure (y axis value) in a column chart.

I wanted to show the top 10 values in the chart for whichever measure was selected by the slicer, but it isn't possible to use the field parameter in the Top N filter. Without the Top N filter there would be far too many values in the chart.

I got it to work by creating a measure using the SWITCH() function as the column in the Top N filter instead (the field parameter is still used as the y axis column).

The syntax of the measure goes like this:

SWITCH_FILTER =
SWITCH(SELECTEDVALUE('slicer_table'[parameter_column]),
"parameter_value_1",[parameter_measure_1],
"parameter_value_2",[parameter_measure_2],
"parameter_value_3",[parameter_measure_3], BLANK())

SWITCH function (DAX) - DAX | Microsoft Learn

[johnt75]

Interesting. What is the 'slicer table' and how is it related to the field parameter ? It is my understanding that you can't use SELECTEDVALUE directly with the table created for a field parameter.

[PowerBiKing]

Hey - so I have my field parameter table, and a separate 'normal' table that I'm using for the slicer. These tables have a relationship with each other. I didn't set it up as a workaround specifically for the purpose of this solution, I had it like this already because I have one slicer table controlling multiple field parameters.

However I did just test my formula by changing the SELECTEDVALUE part to a column from the field parameter table (instead of the equivalent column in my slicer table) and it does seem to work too. 

[bengtsson_peter]

Do you mean add your suggested measure to the visual's filter and then choose Top N? That doesn't work for measures.

Or do you mean add your suggested measure to the "By value" slot? I tried but it doesn't affect the filtering.

Or did you mean something else?

For now, my workaround is to use bookmarks and a Bookmark Navigator instead. That works just fine but I would really like to learn a way to use Top N filtering with field parameters 🙂

[johnt75]

I had meant to add it to the By Value slot, not sure why that wouldn't work in your case.

It would be great to be able to use field parameters in a TOPN filter, maybe add it as an idea at ideas.powerbi.com

[Anonymous]

Did you figure out how to use field parameter to update the value in the TOPN filter by value slot? @johnt75 

[johnt75]

Not sure if this will work, but try creating a measure as

Total time spent = SUM('MyTable'[TimeSpent])

and add that as a TOPN filter rather than the column.