Forum Discussion
Sorting a table variable within DAX
Hi Icey, would be good to do so in case I'm 100% sure that _Table1 is already sorted, but it's not a case. It depends on the dataset (which is updating on daily basis). To avoid any mistake I want to force data in _Table1 to be sorted.
Basically, my question is how to modify a variable (in this case _Table1) without creating another variable? I want to achieve something like the code below:
TestTable =
...//some previous steps in DAX measure
//Initialize a variable _Table1
VAR _Table1 = SUMMARIZE(
FILTER(
ALL(ProductTable);
AND(ProductTable[U_LOCAL_ITEM] IN _SKU; AND(ProductTable[*STARTDATE] >=_MinDate; RELATED(LOC[*LOC]) in _LOC) )
);
ProductTable[*STARTDATE];
"FrcstTemp";
SUM(ProductTable[Forecast]);
"DateTemp";
ProductTable[*STARTDATE]
)
//modify _Table1 variable without creating a new one (i.e. _Table2 from my initial post)
_Table1 = SAMPLE(
COUNTROWS(_Table1);
_Table1;
ProductTable[*STARTDATE];
ASC
)
... //next steps in DAX measure
Return //some result
But, as you can imagine, it doesn't work...
Hi all,
So after another deep search on web I found an answer here (https://community.powerbi.com/t5/Desktop/DAX-How-update-a-variable-value-after-having-defined-the/td-p/568071) and here (https://exceleratorbi.com.au/using-variables-dax/).
Basically my issue was not about sorting itself, but about redefining the variable within DAX (as you can do in other programming languages). Well, it seems you can't do it in DAX and in case you have created Varibale1 and want to perform an action on it you'll have to define a Variable2 for this.
Have a nide day!