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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
jscivias
Frequent Visitor

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors