How do I set a maximum value for each repeated cell in a slicer?

[redactor]

Hi all,

A beginner in using PowerBi. I have a slicer for each department that have taken various quizzes within the year. Each department have different number of people and I set it so that the dashboard will display data according to which department is chosen in the slicer.

I wish to show a rate of completion for each department but I need to be able to set the maximum number of people within each department in the slicer. Is there any way to do so?

[danextian · Accepted solution]

Hi @redactor 

What do you mean by this?

I wish to show a rate of completion for each department but I need to be able to set the maximum number of people within each department in the slicer.

Or do you mean the max people for each dept? You can crete something like

CALCULATE ( DISTINCTCOUNT ( data[people] ), ALLEXCEPT ( data, data[dept] ) )

This will work either as a calculated column or a measure

[123abc · Accepted solution]

Here’s a step-by-step guide to help you achieve this based on assumpiton:

1. Create a Table with Department Data: Ensure you have a table that includes the department names and the number of people in each department.

2. Create a Measure for Maximum Value: You can create a measure to calculate the maximum number of people for each department. Here’s an example DAX formula:

   MaxPeople = CALCULATE(MAX(YourTable[NumberOfPeople]), ALLEXCEPT(YourTable, YourTable[Department]))

3. Create a Slicer: Add a slicer to your report and use the department column as the field for the slicer.

4. Create a Calculated Column for Completion Rate: Create a calculated column to calculate the completion rate based on the maximum number of people. Here’s an example:

   CompletionRate = DIVIDE(YourTable[CompletedQuizzes], [MaxPeople])

5. Add the Completion Rate to Your Visuals: Use the CompletionRate column in your visuals to display the rate of completion for each department.

6. Set the Maximum Value in the Slicer: Unfortunately, Power BI slicers do not support setting a maximum value directly. However, you can control the slicer values by creating a custom table or using a measure to filter the slicer values dynamically.

Here’s an example of how you can create a custom table to control slicer values:

SlicerTable = 
ADDCOLUMNS(
    SUMMARIZE(YourTable, YourTable[Department]),
    "MaxPeople", [MaxPeople]
)

Then, use this SlicerTable in your slicer to ensure it only shows departments with the maximum number of people.

If you need more detailed guidance or run into any issues, feel free to ask! How are you finding Power BI so far?

Also check given below link ... may they help you.

(1) Solved: Max selected value in a slicer - Microsoft Fabric Community

Solved: How to set default MAX date in Slicer - Microsoft Fabric Community

Force selection and max limit of selections in Pow... - Microsoft Fabric Community

Set a Dynamic/Default/Changing Date in Power BI Date Slicer

[Anonymous · Accepted solution]

Thanks for the replies from 123abc, danextian and Kedar_Pande.

Hi @redactor ,

Based on your description, I tried to fulfill your requirement by creating numeric range parameters for different departments, here are the exact steps:

1. Here is the test data:

2. Under the Modeling tab, create four parameters:

3.Create two measures:

Peoplenumber = SWITCH(MAX('Table'[Department]),"HR",'HR 2'[HR Value],"IT",'IT 2'[IT Value 2],"Sales",'Sales'[Sales Value],"Marketing",'Marketing'[Marketing Value])

CompletionRate = DIVIDE(MAX('Table'[CompletedQuizzes]),[Peoplenumber])

4. Result:

Best Regards,
Zhu
Community Support Team

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

[123abc · Accepted solution]

Here’s a step-by-step guide to help you achieve this based on assumpiton:

1. Create a Table with Department Data: Ensure you have a table that includes the department names and the number of people in each department.

2. Create a Measure for Maximum Value: You can create a measure to calculate the maximum number of people for each department. Here’s an example DAX formula:

   MaxPeople = CALCULATE(MAX(YourTable[NumberOfPeople]), ALLEXCEPT(YourTable, YourTable[Department]))

3. Create a Slicer: Add a slicer to your report and use the department column as the field for the slicer.

4. Create a Calculated Column for Completion Rate: Create a calculated column to calculate the completion rate based on the maximum number of people. Here’s an example:

   CompletionRate = DIVIDE(YourTable[CompletedQuizzes], [MaxPeople])

5. Add the Completion Rate to Your Visuals: Use the CompletionRate column in your visuals to display the rate of completion for each department.

6. Set the Maximum Value in the Slicer: Unfortunately, Power BI slicers do not support setting a maximum value directly. However, you can control the slicer values by creating a custom table or using a measure to filter the slicer values dynamically.

Here’s an example of how you can create a custom table to control slicer values:

SlicerTable = 
ADDCOLUMNS(
    SUMMARIZE(YourTable, YourTable[Department]),
    "MaxPeople", [MaxPeople]
)

Then, use this SlicerTable in your slicer to ensure it only shows departments with the maximum number of people.

If you need more detailed guidance or run into any issues, feel free to ask! How are you finding Power BI so far?

Also check given below link ... may they help you.

(1) Solved: Max selected value in a slicer - Microsoft Fabric Community

Solved: How to set default MAX date in Slicer - Microsoft Fabric Community

Force selection and max limit of selections in Pow... - Microsoft Fabric Community

Set a Dynamic/Default/Changing Date in Power BI Date Slicer

[danextian · Accepted solution]

Hi @redactor 

What do you mean by this?

I wish to show a rate of completion for each department but I need to be able to set the maximum number of people within each department in the slicer.

Or do you mean the max people for each dept? You can crete something like

CALCULATE ( DISTINCTCOUNT ( data[people] ), ALLEXCEPT ( data, data[dept] ) )

This will work either as a calculated column or a measure

[Kedar_Pande]

@redactor 

Create a Measure

Completion Rate =
VAR TotalCompleted =
CALCULATE(
COUNTROWS('Main Dataset'),
'Main Dataset'[Quiz Completed] = "Yes"
)
VAR MaxPeople =
MAX('Department Capacity Table'[Max People])
RETURN
DIVIDE(TotalCompleted, MaxPeople, 0)

Create a visual (e.g., a bar or card) to show the Completion Rate.
Optionally, add the Max People card visual.
Add the slicer for the Department.

💌 If this helped, a Kudos 👍 or Solution mark ✔️ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn