Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jscivias
Helper I
Helper I

Turning numerical slicer into custom ranges

Hello, I currently have a slicer based on employee numbers:

 

jscivias_0-1657915275956.png

But I'd like to turn this into custom ranges, e.g. 1-19, 20-99, 100-249, 250-499, 500+. 

I read through some posts in the past that first suggested to create a new table with ranges as follows:

 

jscivias_1-1657915401573.png

 

but after this, i'm not sure what to do so that when i add a new slicer using Emp. range, every visual on my dashboard page would filter out data that do not belong to the selected employment category? 

 

Thanks for your help. 

 

 

 

 

 

1 ACCEPTED SOLUTION

Write a Switch statement to group your Firms by Employee range
Condition Column
= Switch( 
True(),
Employees <20, "1-19",
Employees <100, "20-99",
etc.

Then use your new conditional column as your slicer.

**note the switch statement above isn't correctly formatted, just as a reference. If you need help writing it, reply to this message and I can help you with it

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

Share some data, describe the question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi, 

suppose I currently have firm level data as in the following table. I would like to create a dashboard with individual firm data plotted on a map and some bar charts, where users can filter all elements in the dashboard by industry, revenue, location and employee slicers. but for the employee slicer specifically, i would prefer to show a slicer with custom ranges of employees like this:

 

jscivias_0-1658154081744.png

so that if a user were to select employment range 100-249 first box, then only those firms with employees in that category will appear in the dashboard map, graphs, etc.. thanks for your help. 

 

Firm name |Employees |location |revenue |industry
a96   
b6   
c550   
d45   
e122   
f5   
g65   
h2   
i2343   
j5   
k55   
l5   
aa111   
bb10   
ab5   

 

 

Write a Switch statement to group your Firms by Employee range
Condition Column
= Switch( 
True(),
Employees <20, "1-19",
Employees <100, "20-99",
etc.

Then use your new conditional column as your slicer.

**note the switch statement above isn't correctly formatted, just as a reference. If you need help writing it, reply to this message and I can help you with it

Thank you so so much!!! I did a search on this topic and tried various different suggestions made by people in the past and scratched my head for an embarrassing amount of time but then your suggestion solved my problem in a matter of a couple of minutes!! I've never heard of a switch statement, does it essentially fill a new column with descriptions in quotation marks based on the 'if' like statement preceding it? Thanks again. 

Correct. It would be the same a nested if statement or a case/when statement in SQL:
Switch(
True(),
If these conditions are met, then return this,
If these, then this, 

If these, then this, 

else do this.

The return value can be a text, number, or any other value you can write in DAX

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.