Forum Discussion

MarkCBB's avatar
MarkCBB
Helper V
9 years ago
Solved

DAX Conditional Number formatting

Hello there,

 

Not sure if this is possible, but I would like to create a DAX measure that toggles between Currency and Whole Number.

i.e. the user can use a slider to switch between viewing Unit sales to Value sales, I through the below would work, but it doesnt.

I have the slicer control working fine:

View Meansure = if(COUNTROWS(ViewValue)>1,[Total Units],IF(LASTNONBLANK(ViewValue[ViewValueType],0)="Units", [Total Units],CURRENCY([Total Value])))
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    My understanding is that if you want conditional formatting, you are stuck with Strings.  If you need to make numeric operations, keep the source information different to the display information.  I.e. you have a Calc measure that does all of the work, and can be use by other measures further down your chain.  Then have a Display measure that is simply the conditional format of your Calc.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi MarkCBB,

     

    You can create a view table to store the select item, then write a measure switch the display item based on slicer.

     

    Table formula:

     

    View Table = UNION(ROW("Name","Currency"),ROW("Name","Whole Number"))

     

     

    Measure:

    View Measure = 
var selector=IF(HASONEVALUE('View Table'[Name]),VALUES('View Table'[Name]),BLANK()) 
return
SWITCH(selector,"Currency",[Total Units],"Whole Number",CURRENCY([Total Units]))

     

     

    Regards,

    Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi MarkCBB

    If you want to show the amount with a leading $ sign, you have to replace the last part of your formula:

     

     

    replace 
    ,CURRENCY([Total Value])
    with 
    ,FORMAT([AOV2],"$ #.##")

     

     

    • MarkCBB's avatar
      MarkCBB
      Helper V
      9 years ago

      Hi Anonymous,

       

      I have tried to do that, but when that is in a chart visual the chart does not know how to display it as it changes the data type into string.

       

      I also tried to wrap the Format in VALUE but Value was not able to convert the String into a number.

       

      Any ideas?

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        My understanding is that if you want conditional formatting, you are stuck with Strings.  If you need to make numeric operations, keep the source information different to the display information.  I.e. you have a Calc measure that does all of the work, and can be use by other measures further down your chain.  Then have a Display measure that is simply the conditional format of your Calc.