Power Pivot: DAX Window Functions (some more spices)

Continuing the article on comparing the capabilities of SQL Server window functions and DAX formulas

In the last article , aspects of the operation of PowerPivot functions were considered and a hypothetical example demonstrated the analogy between the window functions sql server and DAX functions.
Let me remind you that the original example suggested finding incorrect data using DAX, namely:
search in the source data (detailed to the lines of the consignment note) names of outlets with more than one address in the city.
As a result, this goal was achieved, but it would be possible to get much more practical benefits if you force PowerPivot to perform certain actions on these occasions. At the same time, this is an excellent occasion to show some more useful things from the DAX arsenal.

A little thought about how best to handle the events of our example (and their nature lies either in moving the point to a new address, or in banal typos when entering the address), the first thing that comes to mind is to standardize such addresses by adding a single address for several different addresses in the “point-of-town” tuple. The most logical in this case will be the assignment of a single address for the entire sales history of the outlet in the city to the last shipping address.
For this, it would be nice first for each “window” [= name of an outlet + city] to get the latest date of activity (sale).
Well, everything is simple, on the sql server the date search request for each atomic entry will look like this:

Select 't1. ', 't1.', 't1.', 't1.', 't1.№ ', 't1. ', 't1., ', 't1.  ', 't1.    ', max( 't1. ') OVER ( partition by 't1.', 't1. ' ) as maxdate from Table as t1 

After reading the previous article, implementing the calculated field in DAX is not difficult:

 MaxDatePosInTown:=CALCULATE(MAX('1'[ ]);ALLEXCEPT('1';'1'[ ];'1'[])) 

Now, having the date of the last sale, it can be used as an auxiliary field to form the corresponding subset.
Functionally, the subset should be formed as follows:
For each record in the initial data set, you need to get a window from the name of the outlet + city and in this window get a subset of records where the date corresponds to the last sale date in this window.
From the list of DAX functions, the FILTER function is the most suitable for this.
Here is a snippet of help:

On DAX, the subset we need now will look like this:

  FILTER( ALLEXCEPT('1';'1'[ ]; '1'[]); '1'[ ]= '1'[MaxDatePosInTown]) 

where in the role of table is the window = point + city, in the role of filtering condition filter: 'Table1' [Date of consignment note] = 'Table1' [MaxDatePosInTown]
')
Pay attention to the condition from the certificate:
“The FILTER function is not used independently, but embedded in other functions, the argument of which should be a table. „
That is, the problem is that from the resulting subset, select any one record (exactly one) and take the column we need from it.
The overall picture of the final formula is already visible:

 =CALCULATE( ('1'[]); FILTER( ALLEXCEPT('1';'1'[ ]; '1'[]); '1'[ ]= '1'[MaxDatePosInTown])) 

(The need for the CALCULATE function is described in the last article )

Among the list of available formulas the most suitable catches the eye LOOKUPVALUE


But attempts to launch it did not bring success. In the same topic I found confirmation from the overseas blog:



Therefore, pay attention to FIRSTNONBLANK, which takes the following arguments:


The only point is that the function requires an expression, but here you can slip it TRUE
As a result, the formula will have the final form:

 =CALCULATE( FIRSTNONBLANK('1'[]; TRUE()); FILTER( ALLEXCEPT('1';'1'[ ];'1'[]); '1'[ ]='1'[MaxDatePosInTown])) 

An analogue in SQL Server is something like:

 with a1 as ( Select 't1. ', 't1.', 't1.', row_number() over (partition by 't1. ', 't1.' order by 't1. ' desc ) as rv from '1' ) Select 't1. ', 't1.', 't1.', 't1.', 't1.№ ', 't1. ', 't1., ', 't1.  ', 't1.    ', 'a1.' as [] from Table as t1 inner join a1 on 't1. '='a1. ' and 't1.'= 'a1.' where a1.rw=1 

The end result from PowerPivot on the end-to-end example will look like this:

PS You can try another such alternative that gives the same result.

 =calculate(LASTNONBLANK('1'[];1);Filter('1';'1'[ ]=earlier('1'[MaxDatePosInTown]) && '1'[ ]=earlier('1'[ ]))) 

But it seems to me that it is somewhat more difficult for perception.

I hope it was interesting.
Ananiev Heinrich.