Display filter-aware count in a slicer

[molegris]

Hi,

I thought I was facing a typical use case but I can't find any resources online that explain how to achieve that.  So I hope I can find help here!

Table 1 : List of programs offered by the school

Table 2 : List of revisions those programs had over the years.

As a user I want to see the number of revisions next to the program title directly in the slicer so I know right away when I select the program how many revisions are included in that selection.

First attempt : Adding a calculated column to the Program table

NewColumn = Programmes[Title] & " - (" & COUNTROWS(FILTER(Revisions, Revisions[ParentProgrammeCode] = Programmes[ProgrammeCode])) & ")"

Issue #1 (see image below) : The number of revision doesn’t seem to be context-aware because it doesn’t change when some revisions are filtered out at the page level.

Second attempt : Solved by creating a measure instead. (see green arrow in image below)

NewMeasure = FIRSTNONBLANK(Programmes[Title],Programmes[Title]) & " - (" & CALCULATE(COUNTROWS(Revisions),ALLSELECTED(Revisions),values(Revisions[ParentProgrammeCode])) & ")"

Issue #2 (see image below) : Power BI doesn’t let me use the measure in the slicer.  I’m forced to use the NewColumn but it provides a bad user experience because the number of revisions displayed in parenthesis doesn’t take the filter into account.

Issue #3 (see image below) : It’s not an issue for me at the moment but I was wondering if it is possible to limit the level of aggregation for a measure.  In this case I would like to limit NewMeasure to never be able to aggregate passed the Program.Code level.

Can't display number of revisions in the slicer

Thank you

--mo

[lbendlin · Accepted solution]

issue #1 - absolutely correct. calculated columns cannot be influenced by filters.

Do you notice that in the "Filters on this page" section you have your solution, ie the "rows"  that are still available based on the filter combinations from the report and the page.  If you check the same on the "Filters for this visual" section you'll get another level of refinement.

Note: you may not be able to add a measure to a slicer but you can add it to a table visual.

[molegris]

Hi,
Thanks for your answer. You made me realized I should try to change the culture and teach our users how to use the filter pane. Even if it's a bit less intuitive than the slicers I think it simplifies the design a lot. There's less "ink" hence more room for visuals. The new motto could be "Less slicers is better!" 🙂

Otherwise the "table visual as slicer" idea is a suitable workaround. I was so entangled into making my first idea work, I’m sad I didn’t think about it myself. Lol Thanks again.

--mo