cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sach
Helper III
Helper III

Filter using slicer by count of something

I have a data set for points scored by players. The GameID is a string, and is unique for each player but two players can have the same GameID (in cases where they were in the opposing teams of a game).

 

 

Player,GameID,Points
A,ID1,12
A,ID2,33
A,ID3,44
A,ID4,6
A,ID5,3
A,ID6,86
A,ID7,43
A,ID8,77
A,ID9,4
A,ID10,94
B,ID3,44
B,ID12,66
B,ID33,8
C,ID5,34
C,ID7,64
C,ID8,55
C,ID44,34
C,ID55,25
C,ID66,3

 

 

I want to display a summary of the players total points and average, as well as use this data in other visuals. Suppose I display a summary in a table like this.

 

Sach_0-1612047760357.png

 

Now I would like the user to give the opportunity to filter this visual using a Slicer, and I want the # games played by each player to be the filter. For instace, say I want the user to be able to fitler this visual and display stats for players who have played 5 or more games, so in this case the visual will exclude Player B after filtering.

 

Due to limitations/reasons in my actual project, I want the Count of GameID (instead of, say, Count of Player or Count of Points) to be the filter field in the Slicer.

 

But now I'm facing a problem because it won't allow me to use the count of GameID in a Slicer. I could create a measure for CountGameID, but it wouldn't let me use a measure in a slicer either.

 

Is there any way to get the result I want? I specifically don't want to use the Filters pane, but use a slicer (or any other visual) to do the filtering.

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@Sach here is the updated measure and between will work

 

Filter Games Count = 
VAR __min = MIN ( 'Filter Games'[Filter Games] ) 
VAR __max = MAX ( 'Filter Games'[Filter Games] ) 
RETURN
IF ( [Games] >= __min && [Games] <= __max , 1, 0 )

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

6 REPLIES 6
parry2k
Super User
Super User

@Sach here is the updated measure and between will work

 

Filter Games Count = 
VAR __min = MIN ( 'Filter Games'[Filter Games] ) 
VAR __max = MAX ( 'Filter Games'[Filter Games] ) 
RETURN
IF ( [Games] >= __min && [Games] <= __max , 1, 0 )

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Yup, that worked like a charm! And I think I got the concept behind now. Appreciate the help.

parry2k
Super User
Super User

so if you want between you just need to tweak the measure






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





mahoneypat
Super User
Super User

You can make a separate table with GENERATESERIES(1,10,1) for example and use the Value column from that in your slicer.  You can then make a measure where the distinct count of Games played equals the SELECTEDVALUE from the slicer.

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


parry2k
Super User
Super User

@Sach use what-if parameter to get this going, see the attached solution, tweak it as per your needs.

 

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Thanks for the reply!

 

This worked - sort of. It definitely works for the lower limit (single filter), but if change the slicer to a 'Between' then even the lower limit doesn't seem to work. I'm guessing I need to add a similar max filter?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.