Reply
Highlighted
Frequent Visitor
Posts: 12
Registered: ‎04-22-2017
Accepted Solution

Switch Function on Disconnected Slicer

Hey Everyone,

 

I wanted to first say thanks to everyone here, I have been on this site a few times a week in my last few months learning DAX and this place has been a huge help. 

 

I am running into an issue with the Switch function so I finally figured I'd post as this is for a report we are launching to our team next week and I need to finish this one final change. 

 

I have created a disconnected slicer for a ranking sheet we are working on. The slicer will allow you to select between "% To Target" "Sales Per Day" and "Total Sales" and see the values and sales people ranked. I already created the measures, and the following formula for the slicer:

 

AG Current Selection = 
SWITCH
	(FIRSTNONBLANK
		('GAT Slicer'[Type],
			'GAT Slicer'[Type]),
				"Sales Per Day",FORMAT([SPD MTD - AG],"$#,##0;($#,##0)"),
				"Total Sales",FORMAT([Total Sales AG],"$#,##0;($#,##0)"),
				"% To Target",FORMAT([% To GAT AG],"Percent")
		)

The original measures are formatted correctly as currency, percent, etc., however, this measure auto defaults to "Text" and I can't change it, so the rankings are wrong. I thought adding the "FORMAT()" function into the switch measure might fix the format issue and override the text formatting, but it's not fixing it. It all appears to be correct, but when sorted, it's sorting like a text column, i.e. $87,600 is sorted right below $9,700, etc.

 

Any help would be greatly appreciated!!


Thanks in advance.


Accepted Solutions
Super User
Posts: 1,627
Registered: ‎07-03-2015

Re: Switch Function on Disconnected Slicer

Yes, it's a problem unfortunately. You could vote for my idea here. 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/15231165-conditional-formatted-me...

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post


All Replies
Super User
Posts: 1,627
Registered: ‎07-03-2015

Re: Switch Function on Disconnected Slicer

Yes, it's a problem unfortunately. You could vote for my idea here. 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/15231165-conditional-formatted-me...

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Frequent Visitor
Posts: 12
Registered: ‎04-22-2017

Re: Switch Function on Disconnected Slicer

[ Edited ]

Thanks for the response. That's a disappointment, but I just voted, let's hope they fix this soon. I had so many cool ideas that this kills. Although, looks like you posted that over a year ago so I won't hold my breath!