Forum Discussion
cuohanele
Helper I
Helper IDYNAMIC 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...
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 ())Add another space and change the 5 to 6.
lbendlin
Super User
Super UserThat'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 ())
cuohaneleHi. This is the same DAX i used. It's returning the column as texts and sorting out of order still