cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Reetz
Helper I
Helper I

Switch function not working with a slicer

Have been trying to figure this out, watched videos etc, but still can't get this to work.

I have a Customer Orders Table for Jan & Feb 2020 and need to annualize Customer Sales and place them in dollar buckets.  Then using a slicer with those buckets, display just those customers and their annualized sales that fall into the sales bucket selected.  In the example below, selecting 500-999 should only display customer 4 in the Sales Buckets Table.  Thanks in advance!!!!!

 

See attached Power BI.  https://www.dropbox.com/home/Power%20BI?preview=Sales+Buckets.pbixCapture.JPG

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

View solution in original post

7 REPLIES 7
AlB
Super User III
Super User III

Hi @Reetz 

Link is not working. Must be missing something

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Cheers 

 

SU18_powerbi_badge

 

https://www.dropbox.com/home/Power%20BI?preview=Sales+Buckets.pbix 

 

Here is the link again.  Clicking on it says that you must download it first. 

 

Or possibly via this link using my shared drive.  https://stryker-my.sharepoint.com/:u:/r/personal/rita_stump_stryker_com/Documents/Power%20BI/Power%2...

Hi,

None of those links work.


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

sorry.  not sure why my links are not working. Hoping these help.  

 

I have 2 Tables: Customer Sales &  Sales Buckets

Customer Sales Table
CustomerSalesDate
1$101/1/2020
2$151/1/2020
3$2001/1/2020
4$501/1/2020
1$52/1/2020
2$602/1/2020
4$552/1/2020
5$1,0002/1/2020
Sales Buckets Table
MinMaxSales Bucket to DisplayIndex
0249<2501
250499250-4992
500999500-9993
100049991000-49994
5000 >=50005

This is an image of my fields and measures:

Capture.JPG

my  4 measures are :

 

m_annualized Sales =
var TTLSales=CALCULATE(SUM('Customer Sales'[Sales]))
return DIVIDE(TTLSales,2)*12
 
m_annualized Sales bucket =
if([m_annualized Sales]<250,"<250",
IF(AND([m_annualized Sales]>=250,[m_annualized Sales]<=499),"250-499",
IF(AND([m_annualized Sales]>=500,[m_annualized Sales]<=999),"500-999",
IF(AND([m_annualized Sales]>=1000,[m_annualized Sales]<=4999),"1000-4999",
">=5000"))))
 
m_Sales all measures =
SWITCH([m_Selected Value],
"<250",[m_annualized Sales bucket]="<250",
"250-499",[m_annualized Sales bucket]="250-499",
"500-999",[m_annualized Sales bucket]="500-999",
"1000-4999",[m_annualized Sales bucket]="1000-4999",
">=5000",[m_annualized Sales bucket]=">=5000")
 
m_Selected Value = SELECTEDVALUE('Sales Buckets'[Sales Bucket to Display])
 
My Slicer uses the field Sales Bucket to Display. 
 
Reattaching my power bi screen print.  In the example below, by selecting slicer option 500-999, my Sales Bucket Table should only show customer # 4.  I'm just not sure what i'm doing wrong.
Capture.JPG

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

View solution in original post

This is Great!!!!  Thank you!  Just curious, I thought I had to use the Switch function to make this work.  Is this just a different approach?

 

Thanks again!!

Thank you.  Yes, a completely different appraoch based on measures only and of course a disconnected table.


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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

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