Fabric User Data Function parameter defaults?

[MangoMagic]

Was trying to create a function which runs a stored proc and returns some results, is there a way to set default values in parameters, e.g. to make parameters optional and default them if no value is given?

I tried following but it doesn't seem to work, the function doesn't even publish when I try and set default values 😞

@udf.connection(argName="sqlDB",alias="Control")
@udf.function()
def GetWatermark(sqlDB: fn.FabricSqlConnection, ID: int, option: str = "Get watermark", description: str = None)-> list:
    option = option.lower()
    
    if option == "get watermark":
        query = "exec exec [dbo].[Get_ETLSettings] @ID = {ID}, @Option = '{option}';"

    #if option == "hour":

    # Establish a connection to the SQL database
    connection = sqlDB.connect()
    cursor = connection.cursor()

    # Execute the query
    cursor.execute(query)

    # Fetch all results
    results = []
    for row in cursor.fetchall():
        results.append(row)

    # Close the connection
    cursor.close()
    connection.close()
        
    return results

[v-karpurapud · Accepted solution]

Hi @MangoMagic 

Thank you for contacting the Microsoft Fabric Community Forum.

Currently, Microsoft Fabric's Python User Defined Functions (UDFs) created with the @udf.function decorator do not allow default values or optional parameters in their definitions. If you try to set a default value for a parameter, such as option: str = "Get watermark", the function will not publish or compile correctly in the Fabric environment.

To avoid issues, make sure all parameters are explicitly defined and provided when calling the function. If you need default behavior, handle it inside the function by checking if a parameter is empty or null and then assigning a value as needed. This method works within the platform's current limitations and ensures your UDFs will run as expected.

from microsoft.fabric import udf

import microsoft.fabric.functions as fn



@udf.connection(argName="sqlDB", alias="Control")

@udf.function()

def GetWatermark(sqlDB: fn.FabricSqlConnection, ID: int, option: str, description: str) -> list:

   

    if not option:

        option = "get watermark"

    else:

        option = option.lower()



    if option == "get watermark":

        query = f"EXEC [dbo].[Get_ETLSettings] @ID = {ID}, @Option = '{option}';"

    else:

      

        query = f"EXEC [dbo].[Get_ETLSettings] @ID = {ID}, @Option = '{option}';"



    connection = sqlDB.connect()

    cursor = connection.cursor()



    cursor.execute(query)

    results = cursor.fetchall()



    cursor.close()

    connection.close()



    return results

Regards,

Karpurapu D,

Microsoft Fabric Community Support Team.

[MangoMagic]

Thank you, that worked 😃

[Sharmilshah]

Hi @v-karpurapud I have a similar problem. In my use case, I am using UDF into powerbi report and passing the parameters through Text slicer. If I dont pass the value from pbi report, it doesn't enabled the button hence, I need to pass the all values irrespective it is required or not. Is there a workaround for same?

[v-karpurapud]

Hi @Sharmilshah 

In Microsoft Fabric, Fabric UDFs require all parameters to be explicitly provided, and the Invoke Function visual in Power BI disables the button whenever any parameter evaluates to BLANK(). Because of this behavior, optional parameters or default values in the UDF signature are currently not supported.

To work around this, keep all parameters mandatory in the UDF signature and implement any “optional/default” logic inside the function by checking whether a parameter is empty or null and assigning a default value as needed. On the Power BI side, ensure that measures mapped to UDF parameters never return BLANK(). This can be achieved using SELECTEDVALUE() with a fallback value or COALESCE() for numeric parameters. For example, return "Get watermark" when no option is selected and "" for optional text fields instead of BLANK().

Regards,

Microsoft Fabric Community Support Team.