cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors