DYNAMIC FORMAT USING DAX FOR SLICER

[cuohanele]

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 · Accepted solution]

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

)

[Anonymous]

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]

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 · Accepted solution]

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

)

[cuohanele]

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:

[lbendlin · Accepted solution]

Add another space and change the 5 to 6.

[cuohanele]

What if there are negative numbers? I tried applying  the same logic of your previous DAX to other columns with negative percentages and negative dollar amounts. I ran into this:

[Anonymous]

Hi

Didn't I say already that you'll be having a pretty hard time to get this to work correctly? And there'll probably always be cases not accounted for... If I were you, I'd stay away from such BRITTLE CODE.

[cuohanele]

That works! Thanks a bunch. This is the final DAX:

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

If there were negative percentages, how will the code look? Ran into that issue

[cuohanele]

How can I go about doing that?

[Anonymous]

Watch this: https://youtu.be/DuBc_71YIaE

[Greg_Deckler]

@cuohanele - Correct, the minute you use FORMAT, everything is a string. You might have some luck with the technique described here: https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

It allows you to keep things numeric but display them differently.

[Anonymous]

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]

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

[lbendlin]

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]

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

[lbendlin]

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

[cuohanele]

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]

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]

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: