Power BI Exercise - Creating a Reusable DAX User-Defined Function

Power BI Exercise - Creating a Reusable DAX User-Defined Function

Package safe percentage logic once and call it from multiple retail measures.

In this guided exercise, you will define a reusable DAX function named SafePercentage. You will save it to the semantic model and call it from Sales, Quantity, and Target measures.

Learning goal: package parameterised DAX logic into a model function that improves consistency and reduces repeated formulas.
DAX UDFFUNCTIONParametersReusable logic

Availability requirement

DAX user-defined functions became generally available in Power BI Desktop and the Power BI Service beginning with the June 2026 release. They require semantic-model compatibility level 1702 or later.

If FUNCTION is not recognised: update Power BI Desktop to a current release and reopen the model. This exercise cannot be completed in an older Desktop version.

Why create a user-defined function?

The model already contains several percentage calculations. Their numerators and denominators differ, but all of them need the same safe division behaviour:

DIVIDE (
    numerator,
    denominator,
    alternate result
)

A user-defined function, or UDF, packages this logic and accepts the changing values as parameters.

SafePercentage reusable DAX function Sales, Quantity, and Target measures pass numerator and denominator values to SafePercentage, which uses DIVIDE and returns a percentage. Category Sales %5,959 ÷ 21,788 Category Quantity %11 ÷ 42 Target Achievement %21,788 ÷ 22,000 SafePercentagenumerator : NUMERICdenominator : NUMERICalternate = 0DIVIDE once · reuse everywhere ResultNumericpercentage The function supplies the method; each measure supplies the context-specific values.

One function centralises the division rule while measures retain their business meaning.

Before you begin

Use the completed retail model and confirm these measures are available:

  • [Total Sales] = 21,788
  • [Total Quantity] = 42
  • [Selected Sales Target] = 22,000 when no single target is selected
If you skipped the Dynamic Sales Target exercise, you can still complete the Sales and Quantity sections. The target-based measure is optional.

Step 1: Open DAX Query View

  1. Select the DAX Query View icon on the left side of Power BI Desktop.
  2. Create a new blank query tab.
  3. Enter the function definition and test query shown in the next step.

Step 2: Define SafePercentage

DEFINE
    /// Safely divides two numeric values.
    /// @param {NUMERIC} numerator - Value being compared.
    /// @param {NUMERIC} denominator - Total or target value.
    /// @param {NUMERIC} alternate - Result when denominator is zero.
    /// @returns A numeric ratio.
    FUNCTION SafePercentage = (
        numerator : NUMERIC,
        denominator : NUMERIC,
        alternate : NUMERIC = 0
    ) =>
        DIVIDE (
            numerator,
            denominator,
            alternate
        )

EVALUATE
ROW (
    "Test 25 of 100", SafePercentage ( 25, 100 ),
    "Test zero denominator", SafePercentage ( 25, 0 )
)

Select Run. The expected results are:

TestExpected result
25 divided by 1000.25
25 divided by zero0

Step 3: Save the function to the model

  1. After the test succeeds, select Update model with changes.
  2. Alternatively, select the Update model: Add new function action displayed above the function definition.
  3. Open Model explorer and expand Functions.
  4. Confirm that SafePercentage appears there.
Naming rule: function names cannot contain spaces. Letters, numbers, underscores, and namespace dots are supported.

Step 4: Create Category Sales Percentage using the UDF

Category Sales % UDF =
SafePercentage (
    [Total Sales],
    CALCULATE (
        [Total Sales],
        REMOVEFILTERS ( DimProduct[Category] )
    )
)

Format the measure as Percentage with two decimal places.

Step 5: Create Category Quantity Percentage

Category Quantity % UDF =
SafePercentage (
    [Total Quantity],
    CALCULATE (
        [Total Quantity],
        REMOVEFILTERS ( DimProduct[Category] )
    )
)

The same SafePercentage function now works with a different base measure.

Step 6: Validate category results

Create a Table visual using DimProduct[Category] and both new measures.

CategorySales % UDFQuantity % UDF
Computing27.35%26.19%
Entertainment27.05%16.67%
Home21.90%21.43%
Mobile23.71%35.71%
Total100.00%100.00%

Step 7: Reuse the function for target achievement

If [Selected Sales Target] exists, create:

Target Achievement % UDF =
SafePercentage (
    [Total Sales],
    [Selected Sales Target]
)
Selected targetTotal SalesExpected achievement
20,00021,788108.94%
22,00021,78899.04%
25,00021,78887.15%

Step 8: Validate measures in DAX Query View

EVALUATE
SUMMARIZECOLUMNS (
    DimProduct[Category],
    "Sales Percentage", [Category Sales % UDF],
    "Quantity Percentage", [Category Quantity % UDF]
)
ORDER BY DimProduct[Category]

The query result grid may display raw decimals. For example, Computing Sales Percentage may appear as approximately 0.2735451.

Step 9: Inspect the saved function

Run:

EVALUATE
INFO.USERDEFINEDFUNCTIONS ()

The result includes function metadata such as its name, expression, state, and any error message. Confirm that SafePercentage has a ready state and no error.

Step 10: Test the alternate parameter

The alternate parameter is optional because its default is zero:

DEFINE
    FUNCTION SafePercentageTest = (
        numerator : NUMERIC,
        denominator : NUMERIC,
        alternate : NUMERIC = 0
    ) =>
        DIVIDE ( numerator, denominator, alternate )

EVALUATE
ROW (
    "Default alternate", SafePercentageTest ( 10, 0 ),
    "Custom alternate", SafePercentageTest ( 10, 0, -1 )
)

The expected results are 0 and -1 respectively.

UDF versus Calculation Group

FeatureDAX UDFCalculation Group
Main purposePackage parameterised logic that measures call explicitly.Transform the measure currently in context.
InputsAccepts named parameters.Uses functions such as SELECTEDMEASURE.
ApplicationCalled by name inside a DAX expression.Applied through a calculation item in a visual, slicer, filter, or measure.
Best useReusable business or mathematical routines.Repeated presentation and analytical transformations across measures.

Knowledge check

  1. What logic does SafePercentage centralise?
  2. What do numerator and denominator represent?
  3. When is the alternate parameter returned?
  4. Why does each measure still define its own denominator context?
  5. How is a UDF different from a Calculation Group?

Troubleshooting

ProblemWhat to check
FUNCTION is underlined as invalid.Use Power BI Desktop June 2026 or later and a compatibility-level 1702 model.
SafePercentage cannot be found in a measure.Save the function to the model and confirm it appears under Model explorer → Functions.
A percentage shows as a decimal.Format the calling measure as Percentage.
Every category shows 100%.Ensure the denominator removes DimProduct[Category].
The function is in an error state.Run INFO.USERDEFINEDFUNCTIONS and inspect ErrorMessage.

Exercise complete

You have created a first-class reusable DAX function, saved it to the semantic model, and called it from multiple measures. Safe division behaviour is now maintained in one place while every measure retains its own filter-context logic.
Completion checklist
  • SafePercentage runs successfully in DAX Query View.
  • The function is saved under Model explorer → Functions.
  • Sales and Quantity percentage measures call the function.
  • Category results match the control percentages.
  • Zero denominators return the selected alternate value.
  • The function metadata reports no error.

References

By infotech.razzi.my