Antriksh Sharma

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,...

Let's say you want Top 2 Colors of each Brand, how would you do that? Here are different ways of computing that. Using INDEX & UNION DEFINE VAR Temp = ADDCOLUMNS ( ALL ( Products[Brand], Products[Color] ), "@Sales", [Sales Amount] ) VAR Uno = INDEX ( 1, Temp, ORDERBY ( [@Sales], DESC ), PARTITIONBY ( Products[Brand] ) ) VAR Dos = INDEX ( 2, Temp, ORDERBY ( [@Sales], DESC ), PARTITIONBY ( Products[Brand] ) ) EVALUATE UNION ( Uno, Dos ) ORDER BY...

Today I’m going to show you an application I built with the help of ChatGPT that generates large volumes of Sales data based on the Microsoft's ContosoRetailDW model.