topic Re: DYNAMIC FORMAT USING DAX FOR SLICER in DAX Commands and Tips

DYNAMIC FORMAT USING DAX FOR SLICER

cuohanele — Thu, 06 Aug 2020 14:59:31 GMT

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:

So it is sorting something like this:

100

200

222

Thanks in advance!

Re: DYNAMIC FORMAT USING DAX FOR SLICER

lbendlin — Thu, 06 Aug 2020 15:51:53 GMT

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

Re: DYNAMIC FORMAT USING DAX FOR SLICER

Anonymous — Thu, 06 Aug 2020 15:50:24 GMT

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.

Re: DYNAMIC FORMAT USING DAX FOR SLICER

Greg_Deckler — Thu, 06 Aug 2020 15:51:05 GMT

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.

Re: DYNAMIC FORMAT USING DAX FOR SLICER

cuohanele — Thu, 06 Aug 2020 15:59:14 GMT

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

Re: DYNAMIC FORMAT USING DAX FOR SLICER

cuohanele — Thu, 06 Aug 2020 16:20:37 GMT

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

Re: DYNAMIC FORMAT USING DAX FOR SLICER

lbendlin — Thu, 06 Aug 2020 16:33:12 GMT

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

Re: DYNAMIC FORMAT USING DAX FOR SLICER

cuohanele — Thu, 06 Aug 2020 16:51:24 GMT

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:

Re: DYNAMIC FORMAT USING DAX FOR SLICER

lbendlin — Thu, 06 Aug 2020 16:54:27 GMT

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

Re: DYNAMIC FORMAT USING DAX FOR SLICER

cuohanele — Thu, 06 Aug 2020 17:03:29 GMT

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:

Re: DYNAMIC FORMAT USING DAX FOR SLICER

Anonymous — Thu, 06 Aug 2020 17:06:28 GMT

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.

Re: DYNAMIC FORMAT USING DAX FOR SLICER

cuohanele — Thu, 06 Aug 2020 17:06:56 GMT

How can I go about doing that?

Re: DYNAMIC FORMAT USING DAX FOR SLICER

Anonymous — Thu, 06 Aug 2020 17:14:52 GMT

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

Re: DYNAMIC FORMAT USING DAX FOR SLICER

lbendlin — Thu, 06 Aug 2020 22:41:46 GMT

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

Re: DYNAMIC FORMAT USING DAX FOR SLICER

cuohanele — Fri, 07 Aug 2020 11:34:50 GMT

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:

Re: DYNAMIC FORMAT USING DAX FOR SLICER

cuohanele — Fri, 07 Aug 2020 11:41:13 GMT

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

)

Re: DYNAMIC FORMAT USING DAX FOR SLICER

lbendlin — Fri, 07 Aug 2020 11:42:52 GMT

Add another space and change the 5 to 6.

Re: DYNAMIC FORMAT USING DAX FOR SLICER

cuohanele — Fri, 07 Aug 2020 11:47:03 GMT

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

Re: DYNAMIC FORMAT USING DAX FOR SLICER

cuohanele — Fri, 07 Aug 2020 14:57:02 GMT

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

Re: DYNAMIC FORMAT USING DAX FOR SLICER

cuohanele — Fri, 07 Aug 2020 15:36:51 GMT

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: