Forum Discussion

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

DYNAMIC FORMAT USING DAX FOR SLICER

Hi everyone! In need of some assistance. I created a dynamic format for a column in my table to be filtered by a specific slicer. Unfortunately, it is sorting the numbers like they are string. I use...
  • cuohanele's avatar
    cuohanele
    6 years ago

    GOT IT!!!

     

    I took the initial code you sent that fixed the percentage values and switched it out with the new line of code but left the currency values as is. THIS IS THE CODE THAT WORKS. Thank you so much!!!

     

     

    Sales$ (Table) =
    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", RIGHT ( " $" & FORMAT ( [Sales$ YoY Gap], "0,0" ), 12 ),
    VALUES ( 'Parameter'[calculations] ) = "Current Year Actuals", RIGHT ( " $" & FORMAT ( [Sales$ CY], "0,0" ), 12 ),
    BLANK ()
    ),
    BLANK ()
    )
  • lbendlin's avatar
    lbendlin
    6 years ago

    Add another space and change the 5 to 6.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

That's what you get when you use FORMAT() - it produces strings.

 

Try this

 

 

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 ())

 

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

Hi. This is the same DAX i used. It's returning the column as texts and sorting out of order still

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    6 years ago

    cuohanele No, it is not the same. There are subtle differences.  Try it out.

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

      Wow! It definitely made a change but we still ran into an issue. It's sorting the 100's like they are the number 10. See below:

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        6 years ago

        try this

         

        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 ())