DYNAMIC SLICER USING DAX

[cuohanele]

Hello,

I have a problem that is 50% solved. I need to sort a column in numerical order but it is sorting as text. Also the column is dynamically filtered by a slicer that contains a percent datatype AND currency datatype. I was able to figure out how to sort the column when the perentage slicer was selected. The DAX was:

Sales$ := IF(ISCROSSFILTERED('Parameter'[calculations]),
SWITCH( TRUE(),
    VALUES('Parameter'[calculations]) = "YoY%",
SWITCH( TRUE(),
ABS([Sales$ YoY%]) < 0.1, FORMAT([Sales$ YoY%],"  0.0%"),
ABS([Sales$ YoY%]) < 1, FORMAT([Sales$ YoY%]," 0.0%"),
FORMAT([Sales$ YoY%],"0%")
),
    VALUES('Parameter'[calculations]) = "YoY Gap", CONCATENATE("$", FORMAT([Sales$ YoY Gap],"0,0")),
    VALUES('Parameter'[calculations]) = "Current Year Actuals", CONCATENATE("$", FORMAT([Sales$ CY],"0,0")),
BLANK()), BLANK ())

Now, when I slice using the currency type, it sorts out of order like the numbers are text. See below. Can we come up with a modification to the DAX above to sort the currency as well? 

[Anonymous]

Did I not give you a solution to this problem in your previous post about it? You chose to ignore it, right? I'll repeat myself quickly here. Use buttons, bookmarks and simple measures for this. Don't try to squeeze a square peg into around hole... like some on this forum instruct you to do.

[cuohanele]

Hi! I ended up doing a bookmark as you suggested actually, but I knew that there was an easy fix to the DAX which was solved as well! I enjoy finding multiple solutions to the same problem just in case soemone else might run into the same issue. I appreciate your advice greatly. Thanks again

[Anonymous]

The issue with the solution you're trying to get is simple. If you format your number as $222.22, then... you'll have the following problems: 1) what about numbers that are greater by orders of magnitude, e.g., $222,222.22? Do you want to show numbers like '$[space space space space...]22.22' and '$222,222.22'? Because they have to be formatted like this to sort correctly. This is not only ugly, it's awfully brittle. What if your numbers, for some reason in the future, will have to be displayed in a different currency? Or, you'll change the granularity of the money unit? Your measure will immediately fall apart... A real solution to a problem is robust and easy to understand and implement. When you create it, you should also think about the future as requirements can change. But do as you wish, of course 🙂I showed you the right way.

[cuohanele]

I seeeeee! Great point! Yeah definitely sounds like it would be an issue if things have to change. Will use your methodology moving forward. It was pretty fast and easy to get done too 🙂 

[amitchandak]

@cuohanele , refer this can help, video from GuyinaCube

https://www.youtube.com/watch?v=vlnx7QUVYME

https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...