User Defined Function - Dynamic Formatting

[christsup3r]

Hi All, is there a way to create a function that will work on dynamic formatting? it seems like you cannot run calculations if you are going to use it within dynamic formatting. I have this code. 

DEFINE 
    FUNCTION Fn_Formatting = (_Measure, _FormatType, _Scale) =>

    VAR v = _Measure
    
    VAR Result = 
        SWITCH(
            TRUE(),
            // Amounts with Scale
            _FormatType = "Amounts" && _Scale = "B", "$" & FORMAT(DIVIDE(v, 1e9), "#,0.0") & "B",
            _FormatType = "Amounts" && _Scale = "M", "$" & FORMAT(DIVIDE(v, 1e6), "#,0.0") & "M",
            _FormatType = "Amounts" && _Scale = "K", "$" & FORMAT(DIVIDE(v, 1e3), "#,0.0") & "K",
            _FormatType = "Amounts", "$" & FORMAT(v, "#,0.0"),
            
            // Numbers with Scale
            _FormatType = "Numbers" && _Scale = "B", FORMAT(DIVIDE(v, 1e9), "#,0.0") & "B",
            _FormatType = "Numbers" && _Scale = "M", FORMAT(DIVIDE(v, 1e6), "#,0.0") & "M",
            _FormatType = "Numbers" && _Scale = "K", FORMAT(DIVIDE(v, 1e3), "#,0.0") & "K",
            _FormatType = "Numbers", FORMAT(v, "#,0.0"),
            
            // Default
            FORMAT(v, "#,0.0")
        )
    RETURN
        Result

is there a work around on this?

Thank you!

[v-echaithra]

Hi @christsup3r ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-echaithra]

Hi @christsup3r ,

Thank you @OwenAuger  for your inputs.

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Best Regards,
Chaithra E.

[OwenAuger]

Hi @christsup3r 

In general, a format string expression should return a format string, rather than the result of formatting a value with a format string.

In your case, if you're wanting format strings to be set based on _FormatType and _Scale parameters, you could rewrite your function as follows (I've made some simplifications as well):

DEFINE 
FUNCTION Fn_Formatting = ( _FormatType, _Scale ) =>
VAR CurrencySymbol =
    SWITCH (
        _FormatType,
        "Amounts", "$",
        ""
    )
VAR NumScaleCommas = SWITCH (
        _Scale,
        "B", 3,
        "M", 2,
        "K", 1,
        0
    )
VAR ScaleCommas = REPT ( ",", NumScaleCommas )
VAR FinalFormatString =
    CurrencySymbol & "#,0" & ScaleCommas & ".0" & "\"
        & _Scale
RETURN
    FinalFormatString

Then you could set the dynamic format string of a particular measure to be an expression like any of these:

Fn_Formatting ( "Amount", "K" )

Fn_Formatting ( "Numbers", "M" )

Does this work for you?

See this post for some further demonstrations of UDFs applied to format strings.