New DAX! User-Defined Functions - Power of Business Intelligence

Now you can create your own DAX functions, reuse them in your reports, and share them with the community.

Power BI Desktop: File | Options and settings | Options | GLOBAL | Preview features – enable DAX user-defined functions. Reopen Power BI.

Now open DAX query view and define your first UDF:

DEFINE FUNCTION HelloWorld =
(
	_World: scalar val
) =>
"Hello " & _World

And click “Update model: Add new function”.

You’ll find it in Functions folder on Model tab of the Data pane in DAX query view.

Or in Functions folder in Tabular Editor.

Once new UDF has been create, you can use it.

Create a measure:

NEW DAX := HelloWorld("Andrzej")

And use it in, for example, a card visual:

Or create more complex UDF:

(
_ValueAC: Scalar VAL,
_ValuePY: Scalar VAL,
_maxValue: Scalar VAL
) =>
//AC – Actual Period (Year) and PY – Previous Period (Year) sales
//Returns an SVG image code with AC and PY bars
// and a vertical line for average (per sales person) AC sales
VAR _ColorAC = “#404040” –RGB(64,64,64)
VAR _ColorPY = “#C6C6C6” –alt. #A6A6A6 = RGB(166,166,166)
VAR _Em = [SVG_CONF_FontSizePt] * 4 / 3 –px
VAR _LabelOffset = 5 –px

VAR _FontWeight = “normal”

VAR _SVG_Width = 300
VAR _SVG_RightPadding = 140
VAR _SVG_ColumnWidth = _SVG_Width – _SVG_RightPadding
VAR _WidthPY =
–width (up to 100%)
DIVIDE ( _ValuePY, _maxValue ) * _SVG_ColumnWidth
VAR _WidthAC =
–width (up to 100%)
DIVIDE ( _ValueAC, _maxValue ) * _SVG_ColumnWidth
VAR _Rank =
–row value rank to ensure correct column sorting (by AC value)
100000
+ RANKX ( ALLSELECTED ( ‘Sales Person’ ), [AC],, ASC )
VAR _SVG = –SVG code
–header
–line (Average AC)
–rectangle for PY
–rectangle for AC
–text data label for AC
–SVG style
“data:image/svg+xml;utf8,” & ” /SortBy:” & _Rank & “/ /axis Y vertical line/ /*PY bar*/ /AC bar/ /AC data label/” & _ValueAC & ” ” & [SVG Style] & ” “
RETURN
_SVG

Change it data category to Image URL:

And use it in a Table visual:

AC, PY = 
SVG_IBCS_ACPY (
    [AC],
    [PY],
    MAX (
        MAXX ( ALLSELECTED ( 'Sales Person' ), [AC] ),
        MAXX ( ALLSELECTED ( 'Sales Person' ), [PY] )
    )
)

Where [AC] - measure than returns actuals, [PY] - measure that returns previous year values, and 3rd parameter calculates max of both values for all sales persons in the visual (required for chart scaling).

Change Image size property of the Table visual.

Result:

Share the article