Sorting a table variable within DAX

[Sergii24]

Hi all,

My DAX consists of few steps and shoud return a table. At some point I'm creating 3 tables one after another.
The first table (VAR _Table1) is summarizing the existing ProductTable, the second one (VAR _Table2) is sorting _Table1, and the third (VAR _Table3) is used to create a column with a cummulative forecast from the previous _Table2.

My question is whether it is possible to get rid of _Table2? I understand that I copy a big amount of data to simply sort it, but can't figure out how to do it inside _Table1.

TestTable = 
VAR _SKU = VALUES(ProductTable[U_LOCAL_ITEM])              // Get the list of selected SKUs
VAR _LOC = VALUES(ProductTable[*LOC])                      // Get the list of selected Locations
VAR _MinDate = MIN(ProductTable[*STARTDATE])               // Get the minimum date for the selected dates
VAR _inventory = SUM(ProductTable[OpenInventory])          // Get open inventory of current date

VAR _Table1 = SUMMARIZE(                                        // Create a table that summarize the forecast by SKU/Date/location key.
                    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]
                )

VAR _Table2 = SAMPLE(                                           // Sort the table1 ascending (from the earliest date)
                    COUNTROWS(_Table1);
                    _Table1;
                    ProductTable[*STARTDATE];
                    ASC
               )
               
VAR _Table3 = ADDCOLUMNS(
                    _Table2;                                    // Create a column of cummulated forecast using table2 data
                    "FrcstCummulTemp";
                    SUMX(
                        FILTER(
                            _Table2;
                            [DateTemp]<=EARLIER([DateTemp])
                        );
                        [FrcstTemp]
                    );
                    "InvTemp";_inventory                        // Add current inventory to each row of table3
                )

Return _Table3

Some curiosity in addition 🙂
I'm curious to know if it's possible to remove variables within DAX? Please correct me if I'm wrong: in my understanding when DAX is performing, it keeps in memory all 3 tables (_Table1, _Table2 and _Table3), while each of them is adding something on top of the previous one. It means from the moment when I created a variable _Table2, I don't need to keep in memory _Table1 anymore (because all relevant data ia already in _Table2), so before initializing _Table3 I would like to delete _Table1 to free some memory and improve DAX performance.

Thanks!

[Icey]

Hi @Sergii24 ,

To my knowledge, Table2 is not necessary. It is not needed to be sorted. You can create Tbale3 just based on Table1. Try it.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sergii24]

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

[Sergii24]

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...) 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!

[amitchandak]

To the question remove the second table, can this help

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[Sergii24]

Thank you for your reply, @amitchandak. The article is very interestng indeed! But I didn't find its relevance to my question.

What I would like to understand is how to deal with _Table2 using DAX measure. Do you think that the use of 3 tables make sense? I feel that there should be a simplier way to sort _Table1, but can't understand how to do it... at least so far 🙂 Would be grateful if you share your thoughts and ideas! Thank you!