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 use...
  • 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 ()
    )
  • lbendlin's avatar
    lbendlin
    6 years ago

    Add another space and change the 5 to 6.

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