UDF with variable table reference and fixed column reference

[brenda89111]

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 with fixed column names, so the structure of the input table is known.

Here is a simplified version of what I'm trying to do:

function MyFunction=

            (

                SelectedTable : TABLE EXPR

            ) =>

            VAR SummarizeTable =

                SUMMARIZECOLUMNS (

                    SelectedTable[Customer Key],

                    SelectedTable

                )

RETURN

COUNTROWS ( SummarizeTable )

The logic works correctly when I manually replace SelectedTable with the actual table name.
However, when I use the parameter as shown above, the measure throws an error saying that the table SelectedTable cannot be found.

I’ve tried different parameter data types, but so far I haven’t found any way to make this work.

Is there a way to pass a table as a parameter while keeping the column names fixed?

Thanks in advance!

[v-prasare · Accepted solution]

Hi @brenda89111,

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 · Accepted solution]

Hi @brenda89111,

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

[v-prasare · Accepted solution]

Hi @brenda89111,

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

[brenda89111]

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]

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?

[brenda89111]

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 )

[Ahmedx]

Pls try this

function MyFunction=
            (
                SelectedTable : anyref EXPR
            ) =>

            VAR SummarizeTable =
                SUMMARIZE(
                    SelectedTable,[Customer Key]
                )
RETURN
COUNTROWS ( SummarizeTable )