One of the most used methods for data analysis is data filtering. Data filtering removes the clutter and makes critical information visible to all users, including marketers, salespeople, data scientists, and financial specialists. A subset of data produced by filtered data is frequently utilized to build new studies. Without a doubt, the most well-known and widely-used business intelligence tool available today, Power BI, provides data filtering through the use of DAX filter capabilities. An iterator function called the DAX filter function creates filtered tables for your data models. The DAX function library has it. As output, each row that matches your condition is returned.
DAX Filter Offset
OFFSET filter Provides a single row that is offset from the current row in the same table, either before or after it. Multiple rows may be returned if the current row cannot be reduced to a single row.
Dataset
Here we are applying the Offset function on the Fact Internet Sales and Dim Date sample dataset. That caters to the sales being done through online channels as can be seen below in the screenshot:
Syntax:
OFFSET ( <delta>[, <relation>][, <orderBy>][, <blanks>][, <partitionBy>] )
Parameters
| Delta | The number of rows to acquire the data from before (negative value) or after (positive value) the current row. Any DAX expression that yields a scalar value is acceptable. |
|---|---|
| relation |
An optional table expression will be used to return the output row. If it is given, it must be the source of all columns in both orderBy and partitionBy. If unavailable:
|
| orderBy |
An ORDERBY () clause is optionally included and contains the columns that specify how each partition is sorted. If unavailable: <relation> must be given explicitly. <OrdersBy> every relational column by default that is not previously set in <partitionBy>. |
| blanks | An enumeration that specifies how to deal with blank data when sorting is optional. This parameter has been set aside for potential use. The only value that is currently available is KEEP (default), where blank values are arranged between zero and negative values for numerical and date variables. Blank values are sorted before all strings, including empty strings, in the case of strings. |
| partitionBy | (Optional) A PARTITIONBY() clause lists the columns that specify how the partitioning of the relationship is defined. If not available, <relation> is handled as a single division. |
Output
It evaluates and gives one or more rows from <relation>.
Example: Measure 1 = SUM(FactInternetSales[SalesAmount])
Measure 2 = CALCULATE(SUM(FactInternetSales[SalesAmount]), OFFSET(-1, , ORDERBY(DimDate[CalendarYear])))
Note:
To assist define the current row on which to work, each “orderBy” and “partitionBy” column must have a matching outer value, with the following behavior:
- The value of the only comparable outer column that exists is used.
- If the corresponding outer column is absent, then:
All orderBy and partitionBy columns without a corresponding outer column will initially be identified using OFFSET. In the parent context of OFFSET, a row is returned for each possible combination of the current values for these columns. These rows are united in the output produced by OFFSET.
- An error is displayed if there are multiple corresponding outer columns.
If each row in the relationship cannot be uniquely identified by the columns supplied in the orderBy and partitionBy clauses, then:
- The least amount of additional columns needed to uniquely identify each row will be sought after by OFFSET.
- Each partition is sorted using this new set of OrderBy columns if such columns are identified, which is done automatically by OFFSET.
- In the absence of such columns, an error is returned.
A blank table is produced if:
- There is no such thing as a “relation” that contains the outer value of an OrderBy or PartitionBy column.
- A shift to a row that doesn’t exist in the partition is brought about by the “delta” value.
An error is returned if OFFSET is used within a calculated column that is specified on the same table as “relation” but “orderBy” is left out.