Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
1
2
200
222
3
33
Thanks in advance!
Solved! Go to Solution.
Hi @cuohanele ,
Using "format" function is to change the number into text format,so go to "column tools " in the menu bar to set the number into "percent" format.
Hi @cuohanele ,
Using "format" function is to change the number into text format,so go to "column tools " in the menu bar to set the number into "percent" format.
They are strings. The FORMAT() function converts values to text. You'd need to have leading zeros to format correctly. I am not aware of a way to conditionally format numbers by number format (currency, percentage, etc.)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUser | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |