Forum Discussion

christsup3r's avatar
christsup3r
New Member
9 months ago
Solved

User Defined Function - Dynamic Formatting

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!

  • OwenAuger's avatar
    OwenAuger
    9 months ago

    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.

     

3 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    9 months ago

    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.

     

  • v-echaithra's avatar
    v-echaithra
    Community Support
    9 months ago

    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.

  • v-echaithra's avatar
    v-echaithra
    Community Support
    9 months ago

    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.