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 used the code below:

 

Sales$ = IF(ISCROSSFILTERED('Parameter'[calculations]),
SWITCH( TRUE(),
    VALUES('Parameter'[calculations]) = "YoY%",
SWITCH( TRUE(),
ABS([Sales$ YoY%]) < 1, FORMAT([Sales$ YoY%],"0.0%"),
ABS([Sales$ YoY%]) >= 1, FORMAT([Sales$ YoY%],"0%"),
BLANK()
),
    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 ())
 
When any of the options in the slicer is picked, it returns this column sorted out of order. See below for visual:
SlicerColumn it is filtered on
So it is sorting something like this:
100
1
2
200
222
3
33
 
Thanks in advance!
  • 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 ()
    )

20 Replies

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    You're returning STRINGS from your measure, not numbers. FORMAT returns STRINGS. Dynamic formatting of measures is only available through calculation groups which are only available when you use Tabular Editor to design measures. It's not possible (yet) through the Power BI Desktop GUI. Sorry.
    • cuohanele's avatar
      cuohanele
      Icon for Helper I rankHelper I
      6 years ago

      Is there a way to create three separate slicers that filter on the same column? By doing that I can change the datatypes for each specifically instead of using one string datatype for all three slicer options

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Instead of all these stunts in the code and workarounds that will soon become hardly readable... why not use the functionality of PBI that's called bookmarks? Instead of a slicer for parameters just use buttons+bookmarks and everything will be easy and simple. By the way, by doing what you're doing right now you're risking that one day there'll be a case you have not thought about and you'll get a messed-up display. Please, for your own good, use buttons, views and bookmarks with simple measures.

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

      Stunts schmunts.  Try this:

       

       

       

      Sales$ :=
IF (
    ISCROSSFILTERED ( 'Parameter'[calculations] ),
    SWITCH (
        TRUE (),
        VALUES ( 'Parameter'[calculations] ) = "YoY%", IF (
            ABS ( [Sales$ YoY%] ) < 1,
            RIGHT ( "  " & FORMAT ( [Sales$ YoY%], "0.0%" ), 5 ),
            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 ()
)

       

       

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

        You are on a roll! So the currency values have been FIXED and the percentages look good with only a very minor issue. See picture below: