Forum Discussion

Brenda_Loznik's avatar
Brenda_Loznik
Advocate II
9 months ago
Solved

UDF with variable table reference and fixed column reference

Hi all, I'm trying to create a user-defined function in DAX where the function receives a table as an input parameter, and then uses that table inside the function. The function is supposed to work ...
Need Help
  • v-prasare's avatar
    v-prasare
    9 months ago

    Hi Brenda_Loznik,

    this is a known DAX limitation and expected behavior, While you can pass a table as a parameter to a DAX user-defined function, DAX does not allow referencing columns with table-qualified notation (Table[Column]) inside the function when the table is coming from a parameter.

     

    To make it work, you first need to rebuild the column names inside the function using SELECTCOLUMNS, and then you can use them normally.

     

     

    Thanks,
    Prashanth

  • v-prasare's avatar
    v-prasare
    9 months ago

    Hi Brenda_Loznik,

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by community members for your issue worked for you or let us know if you need any further assistance?

     

     

    Your feedback is important to us, Looking forward to your response

    Thanks,

    Prashanth

  • Brenda_Loznik's avatar
    Brenda_Loznik
    6 months ago

    In case anyone else runs into the same issue,  After the february 2026 update I now have a working solution:

        function my_function =
                (
                InputTable : ANYREF EXPR
                            ) =>

                var TableName =  TABLEOF ( InputTable)

                RETURN 
      -- Here you can put the logic that you want to repeat for every InputTable --




Brenda_Loznik's avatar
Brenda_Loznik
Advocate II
9 months ago

Thanks for your reply. 
Unfortunately this doesn't work. "Summarize expects a column name as argument". 

I also use some other DAX functions in my UDF. The Dax isn't the problem, because it works outside of the UDF. It is about how to correctly apply a table reference in the UDF.

Ahmedx's avatar
Ahmedx
Super User
9 months ago

I don't see any difference between what I proposed and what you're actually getting.
Can you explain specifically what you're hoping to achieve, using a real-life example?

  • Brenda_Loznik's avatar
    Brenda_Loznik
    Advocate II
    9 months ago

    I'm trying to calculate unique combinations of a subset of columns in different fact tables. 
    In VAR Combos you see a subset of this columns, but the actual list is longer.

    IN VAR FilteredCombos I apply different filtering steps to only keep the records I'm interested in. I can't share my full code, but this is a subset of what I'm doint.

    The end result is a count of unique combinations that meet my requirements. 
    I currently have to repeat this long measure for every fact table I want to have this count in, so that is why I want to have a UDF. 

     

    A measure could look like this: 
    My Measure1 = My_Function(FACT_SALES)

    My Measure2 = My_Function(FACT_ORDERS)

    The measures keep throwing errors "Cannot find table 'SelectedTable'."
    The function works fine when I replace "SelectedTable" by FACT_SALES or FACT_ORDERS. That is why I know the DAX in itself is fine, but the table reference doesnt work properly.

    function My_Function =
                (
                    SelectedTable : TABLE EXPR
                ) =>

                VAR Combos =
                          SUMMARIZECOLUMNS(
                        SelectedTable[Customer Key],
                      SelectedTable[Subject  Key]
                      SelectedTable
                    )

                VAR FilteredCombos =
                    FILTER (
                        Combos,
                        NOT (
                        UPPER (        
                            LOOKUPVALUE (
                                DIM_SUBJECT[Subject],
                                DIM_SUBJECT[Subject Key], SelectedTable[Subject  Key]
                            )
                    ) = "UNKNOWN"   -- criteria 1: the subject cannot be Unknown.
                    )
                )

                RETURN
                COUNTROWS ( FilteredCombos )