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.

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

        We're getting somewhere! So it sorted the percentages right! But now the currency values are out of order (YOY GAP and Current Year Actuals). See below: