User Defined FUNCTION in a DAX Query

In this blog let's take a look at how to use DAX's UDF in a DAX Query that mixes TABLE, COLUMN, MEASURE, and FUNCTION keywords.

We start with a UDF that returns Dates[Year] by the position that we pass, the focus is primarily on First and Last values when there are Years in Dates table that do not have a row in Sales table.

Following UDF code returns First and Last Years:

DEFINE  
    FUNCTION FxGetValueByPosition = (
        N: INT64 VAL,
        OrderByCol: ANYREF EXPR,
        SortOrder: ANYREF EXPR
    ) =>
        INDEX ( N, ORDERBY ( OrderByCol, SortOrder ) )
		
EVALUATE    
    SUMMARIZECOLUMNS (
		Dates[Year],
		"Sales", [Sales Amount],
		"FirstYear", FxGetValueByPosition ( 1, Dates[Year], ASC ),
		"LastYear", FxGetValueByPosition ( 1, Dates[Year], DESC )
    )

For example the above code returns 2020 and 2026.

However it will return Blank if we were to inject those into filter context with CALCULATE as both those years do not have any row in Sales.

EVALUATE    
    SUMMARIZECOLUMNS (
		Dates[Year],
		"Sales", [Sales Amount],
		"FirstYear", FxGetValueByPosition ( 1, Dates[Year], ASC ),
		"LastYear", FxGetValueByPosition ( 1, Dates[Year], DESC ),
		"First Year Sales", 
			CALCULATE ( 
				[Sales Amount],
				FxGetValueByPosition ( 1, Dates[Year], ASC )
			),
		"Last Year Sales",
			CALCULATE ( 
				[Sales Amount],
				FxGetValueByPosition ( 1, Dates[Year], DESC )
			)
    )
 ORDER BY Dates[Year] ASC

So how to return sales of 2021 and 2025? By building the right SourceTable/Relation:

CALCULATETABLE ( 
	SUMMARIZE ( Sales, Dates[Year] ),
	ALLSELECTED ( Dates )
)

This code will pass in only those Years that have at least one row in the Sales table but also considering that there might be a user selection on the Dates table hence ALLSELECTED.

Now we need to change the function definition because by default if the Relation argument of Window functions is omitted then the engine does the ALLSELECTED of columns used in the ORDERBY.

DEFINE  
	FUNCTION FxGetValueByPosition = (
		N: INT64 VAL,
		RelationTable: TABLE EXPR,
		OrderByCol: ANYREF EXPR,
		SortOrder: ANYREF EXPR
	) =>
		INDEX ( N, RelationTable, ORDERBY ( OrderByCol, SortOrder ) )

Now should we pass that custom SourceTable that uses CALCULATETABLE/SUMMARIZE for each call to that function or can we do something better?

Well at least for DAX Queries we can use the TABLE keyword to create a query table.

TABLE SourceTable = 
	CALCULATETABLE ( 
		SUMMARIZE ( Sales, Dates[Year] ),
		ALLSELECTED ( Dates )
	)

Complete code till this point:

DEFINE  
	FUNCTION FxGetValueByPosition = (
		N: INT64 VAL,
		RelationTable: TABLE EXPR,
		OrderByCol: ANYREF EXPR,
		SortOrder: ANYREF EXPR
	) =>
		INDEX ( N, RelationTable, ORDERBY ( OrderByCol, SortOrder ) )

	TABLE SourceTable = 
		CALCULATETABLE ( 
			SUMMARIZE ( Sales, Dates[Year] ),
			ALLSELECTED ( Dates )
		)

EVALUATE    
	SUMMARIZECOLUMNS (
		Dates[Year],
		"Sales", [Sales Amount],
		"FirstYear", FxGetValueByPosition ( 
			1, SourceTable, SourceTable[Year], ASC 
		),
		"LastYear", FxGetValueByPosition ( 
			1, SourceTable, SourceTable[Year], DESC 
		)
	)
ORDER BY 
	Dates[Year] ASC

Now here is a thing, at this point the Value returned by the function calls inside SUMMARIZECOLUMNS have got nothing to do with the Dates[Year] that's because both Dates[Year] and SourceTable[Year] have different lineage plus there is no relationship between SourceTable and Sales.

So if we try to compute Sales Amount it will simply return Sales of Current Year because the engine is trying to inject the Year returned by FxGetValueByPosition into Filter Context but it can't filter Sales table.

What can we do now? We can use TREATAS! We create 2 query MEASURE, both will use the Table returned by query TABLE SourceTable.

Sales First Year:

MEASURE SourceTable[Sales First Year] = 
	VAR FirstYear = 
		FxGetValueByPosition ( 
			1, 
			SourceTable, 
			SourceTable[Year], 
			ASC 
		)
	RETURN
		CALCULATE ( 
			[Sales Amount], 
			TREATAS ( FirstYear, Dates[Year] )
		)

Sales Last Year:

MEASURE SourceTable[Sales Last Year] = 
	VAR LastYear = 
		FxGetValueByPosition ( 
			1, 
			SourceTable, 
			SourceTable[Year], 
			DESC 
		)
	RETURN
		CALCULATE ( 
			[Sales Amount], 
			TREATAS ( LastYear, Dates[Year] )
		)

Finally to get rid of the Blank Rows in Sales we can create a query COLUMN and use it as a Filter inside SUMMARIZECOLUMNS.

COLUMN Dates[HasSales] =
	NOT ISEMPTY ( CALCULATETABLE ( Sales ) )

Complete code:

DEFINE  
	FUNCTION FxGetValueByPosition = (
		N: INT64 VAL,
		RelationTable: TABLE EXPR,
		OrderByCol: ANYREF EXPR,
		SortOrder: ANYREF EXPR
	) =>
		INDEX ( N, RelationTable, ORDERBY ( OrderByCol, SortOrder ) )

	TABLE SourceTable = 
		CALCULATETABLE ( 
			SUMMARIZE ( Sales, Dates[Year] ),
			ALLSELECTED ( Dates )
		)

	MEASURE SourceTable[Sales First Year] = 
		VAR FirstYear = 
			FxGetValueByPosition ( 
				1, 
				SourceTable, 
				SourceTable[Year], 
				ASC 
			)
		RETURN
			CALCULATE ( 
				[Sales Amount], 
				TREATAS ( FirstYear, Dates[Year] )
		)
		
	MEASURE SourceTable[Sales Last Year] = 
		VAR LastYear = 
			FxGetValueByPosition ( 
				1, 
				SourceTable, 
				SourceTable[Year], 
				DESC 
			)
		RETURN
			CALCULATE ( 
				[Sales Amount], 
				TREATAS ( LastYear, Dates[Year] )
			)
			
	COLUMN Dates[HasSales] =
		NOT ISEMPTY ( CALCULATETABLE ( Sales ) )
		
EVALUATE
	SUMMARIZECOLUMNS (
		Dates[Year],
		TREATAS ( { TRUE () }, Dates[HasSales] ),
		"Sales", [Sales Amount],
		"First Year",
			FxGetValueByPosition (
				1,
				SourceTable,
				SourceTable[Year],
				ASC
			),
		"Last Year",
			FxGetValueByPosition (
				1,
				SourceTable,
				SourceTable[Year],
				DESC
			),
		"Sales First Year", [Sales First Year],
		"Sales Last Year", [Sales Last Year]
	)
ORDER BY Dates[Year] ASC

YouTube video for this post: