Forum Discussion
Solved
How to reference column in table variable (after TOPN and CALCULATETABLE)?
I'm trying to select a single value (TOP 1) of a table variable which has multiple columns which where used for filtering and sorting. I made an example in DAX.do (https://dax.do/ha0qItKqNlnWHP/)...
You can reference the column in the original table since the lineage of the virtual table is maintained. In the measure I defined below, notice the second argument of MAXX: it's the underlying table/column. I had to wrap the result in curly braces since this tool returns only tables (in your model, you won't have to do this).
DEFINE VAR aaa = TOPN ( 1, ADDCOLUMNS ( VALUES ( 'Product'[Product Name] ), "myTotal", [Sales Amount] ), [myTotal], DESC ) MEASURE 'Product'[Top Product Name] = MAXX ( aaa, 'Product'[Product Name] ) EVALUATE { [Top Product Name] }
You can reference the column in the original table since the lineage of the virtual table is maintained. In the measure I defined below, notice the second argument of MAXX: it's the underlying table/column. I had to wrap the result in curly braces since this tool returns only tables (in your model, you won't have to do this).
DEFINE
VAR aaa =
TOPN (
1,
ADDCOLUMNS ( VALUES ( 'Product'[Product Name] ), "myTotal", [Sales Amount] ),
[myTotal], DESC
)
MEASURE 'Product'[Top Product Name] =
MAXX ( aaa, 'Product'[Product Name] )
EVALUATE
{ [Top Product Name] }
Thank you! Worked perfectly!
