Forum Discussion

sparta1000's avatar
sparta1000
Frequent Visitor
3 years ago

Looking for equivalent of DAX FORMAT function except does NOT convert numbers to text

The DAX below uses calculation IDs from a disconnected table to assign measures to sections of a P&L's actuals. However, the most trivial part of the code i.e., the two references to the FORMAT funct...
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

sparta1000 

There is no FORMAT function in this measure, however, the empty sting "" will generate error when performing calculations. When referring to this measure in other formulas you need to wrap it with VALUE function. This will eliminate the effect of the empty string ""

However, unless you want to force showing the blank cell in your matrix (which Idon't think is required in your case) I would suggest just to not use the empty string in your formula. In fact the whole condition is not require as the result will be blank by default. 

07.311m Var % Act vs Bud :=
SWITCH (
    MAX ( dPnLSkeleton[CalculationID] ),
    1, [07.301m CC Var % Act Rev Less Threshold Rev],
    2, [07.302m CC Var % Threshold CoR Less Act CoR],
    3, [07.303m CC Var % Act Gross Profit Less Threshold Gross Profit],
    5, [07.305m CC Var % Threshold S&M Less Act S&M],
    6, [07.306m CC Var % Threshold R&D Less Act R&D],
    7, [07.307m CC Var % Threshold G&A Less Act G&A],
    8, [07.308m CC Var % Threshold Opex SMRDGA Less Act Opex SMRDGA],
    9, [07.309m CC Var % Act EBITDAR Less Threshold EBITDAR]
)
  • sparta1000's avatar
    sparta1000
    Frequent Visitor
    3 years ago

    Well no luck.

     

    If I write "Test Measure":= 1 + the measure below:


     02.111mTEST CC Act "data" Complete P&L Expand:=VAR AssignIDsToMeasures = SWITCH(TRUE(),
    MAX(dPnLSkeleton[CalculationID]) = 1, [02.101m CC Actuals Revenue],
    MAX(dPnLSkeleton[CalculationID]) = 2, [02.102m CC Act CoR],
    MAX(dPnLSkeleton[CalculationID]) = 3, [02.103m CC Act Gross Profit],
    MAX(dPnLSkeleton[CalculationID]) = 4, FORMAT( [02.104m CC Act Gross Profit %] , "0.0%" ) ,
    MAX(dPnLSkeleton[CalculationID]) = 5, [02.105m CC Act Opex S&M],
    MAX(dPnLSkeleton[CalculationID]) = 6, [02.106m CC Act Opex R&D],
    MAX(dPnLSkeleton[CalculationID]) = 7, [02.107m CC Act Opex G&A],
    MAX(dPnLSkeleton[CalculationID]) = 8, [02.108m CC Act Opex S&M, R&D, G&A],
    MAX(dPnLSkeleton[CalculationID]) = 9, [02.109m CC Act EBITDAR],
    MAX(dPnLSkeleton[CalculationID]) = 10, FORMAT( [02.110m CC Act EBITDAR %] , "0.0%") )
    RETURN
    IF (AssignIDsToMeasures = 0, BLANK(),
    AssignIDsToMeasures)

     

    It errors out because FORMAT converts the data type from number to text (why you would ever want your number data type to suddenly change to text and therefore instantly kill any follow on calculations I have no idea). All I want is to nullify this inexplicable behavior from FORMAT. I just need it to leave the data type alone - but with a little tweak to the number format in two cases. I can't figure out how to apply VALUE or CONVERT to the code to achieve this despite considerable efforts.

     

    If anyone has any ideas please let me know. Sucks being defeated by such a trivial issue. At this point I'd be happy with an explanation as to why FORMAT was designed to be so destructive -- what could possibly be the point?