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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Reetz
Helper II
Helper II

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
Super User

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/

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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