Forum Discussion
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/) to illustrate the problem (simplified without sorting mechanism):
DEFINE
var aaa =
TOPN (
1,
ADDCOLUMNS (
VALUES ( 'Product'[Product Name] ),
"myTotal", [Sales Amount]
),
[myTotal],
DESC
)
EVALUATE
aaa
"aaa" return two colums in 1 row. I want to access only the "Product Name" column. I need the intermediate variable "aaa".
It's not possible to select it by "aaa[Product Name]". I found: https://community.powerbi.com/t5/Desktop/DAX-Is-it-possible-to-refer-to-columns-of-a-table-variable/td-p/568032 but I'm unable to translate this to my situation.
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] }
2 Replies
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!
