Forum Discussion

cuohanele's avatar
cuohanele
Icon for Helper I rankHelper I
6 years ago

DYNAMIC SLICER USING DAX

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? 

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    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's avatar
      cuohanele
      Icon for Helper I rankHelper I
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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.