topic Re: User Defined Function - Dynamic Formatting in DAX Commands and Tips

User Defined Function - Dynamic Formatting

christsup3r — Fri, 17 Oct 2025 03:01:17 GMT

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!

Re: User Defined Function - Dynamic Formatting

OwenAuger — Fri, 17 Oct 2025 06:45:15 GMT

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.

Re: User Defined Function - Dynamic Formatting

v-echaithra — Thu, 23 Oct 2025 07:19:42 GMT

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.

Re: User Defined Function - Dynamic Formatting

v-echaithra — Mon, 27 Oct 2025 16:21:58 GMT

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.