How can I Use DAX Functions (INDEX, OFFSET, and WINDOW) for Powerful Power BI Visual Table Calculations?📊

Create the table showing the total orders on the basis of region. Let’s add an INDEX to the table. You can use the field “Sales Territory Key” for this.

If we want to calculate the orders for Germany. Let’s start with the INDEX function. First of all, create a new measure.

Let the measure be called the total order index since we will change the initial context. After that, we start with “Calculate”.

Secondly, call the total order, go to the filter section, and then write the INDEX function.

INDEX parameter retrieves a row at an absolute (specified by the position of the parameter) within the specified partition sorted by the specified order or on the axis specified.

INDEX(<position>[, <relation>][, <orderBy>][, <blanks>][, <partitionBy>])

Calculate the orders for Germany, and write 8 in the index, so there are 8 rows in the visible Table. Go to the second parameter, now let’s specify the relationship with the visible table, 8 rows indicate the 8 rows in the whole table. Therefore, call the ALL function and type the visible table. (If we want to specify the order from bottom to top, we can do this by writing a negative number in the position.😉)

If you want the index to be in the order you want, then you can use the ORDERBY parameter of the function. (Sort by the Region names in the table.) For example, to calculate the order for Canada, you can write “2” in the index.

INDEX = 
CALCULATE(
    [Total Orders],
    INDEX(2,ALL(AW_Territories_Lookup[SalesTerritoryKey],AW_Territories_Lookup[Region]),ORDERBY(AW_Territories_Lookup[Region])))

We use INDEX for comparison purposes in analysis.

Let’s see what the OFFSET function does. OFFSET function returns a single row that is positioned either before or after the current row within the same table, by a given offset. Multiple rows may be returned if the current row cannot be deduced to a single row.

OFFSET ( <delta>[, <relation>][, <orderBy>][, <blanks>][, <partitionBy>] )

Create a new measure. The name of the Total Order OFFSET. Let’s calculate the order that precedes it for each row in the visual table for this, type the position parameter “-1”, specify the related table, call the retrieve function, and type the “Sales Territory Key”, followed by the “Region”.

OFFSET = 
 CALCULATE(
     [Total Orders], OFFSET(-1, ALL(AW_Territories_Lookup[SalesTerritoryKey],AW_Territories_Lookup[Region]), ORDERBY(AW_Territories_Lookup[Region],ASC)))

Since no such function works at the visual level, it took a lot of work to calculate the previous row with a DAX. If you want, you can change the position parameter.

OFFSET and INDEX functions return a single row. However, the WINDOW function returns the area to the specified line spacing again. Let’s create a new measure.

The WINDOW function returns multiple rows which are positioned within the given interval.

WINDOW ( from[, from_type], to[, to_type][, <relation>][, <orderBy>][, <blanks>][, <partitionBy>] )

Remember that every line is in the zeroth position before the range starts. For each line and If we want to sum in the preceding line, make the beginning -1 and the end 0.

WINDOW = 
CALCULATE(
    [Total Orders],
    WINDOW(-1,REL,0,REL,ALL(AW_Territories_Lookup[SalesTerritoryKey],AW_Territories_Lookup[Region]),ORDERBY(AW_Territories_Lookup[Region])))

You can add a slicer to your report and change the position number. So you have the flexibility to choose any range from the slicer.

For example, if you do the position “-10” in this example you can get a cumulative total.

For example, you can calculate the total order only for Australia and Canada by sorting measures to make the position 1 ABS and 2 ABS.

Calculate in each row.

For example, you can write the grand total order in each row in the table.

1, ABS, -1,ABS

If you want to calculate the cumulative total, the relative value from the absolute first position that is, you need to add up to each line.

1,ABS, 0, REL

Let’s look at the other parameter of the WINDOW function, the PARTITIONBY parameter. If we want a continent-specific total, we need to divide the visible table first. PARTITIONBY defines the columns used to partition a window function’s <relation> parameter.”

WINDOW_PARTITIONBY = 
CALCULATE(
    [Total Orders], WINDOW(1, ABS,-1,ABS, ALL(AW_Territories_Lookup[Continent],AW_Territories_Lookup[SalesTerritoryKey],AW_Territories_Lookup[Region]),ORDERBY(AW_Territories_Lookup[Continent]), ,PARTITIONBY(AW_Territories_Lookup[Continent])))

👉 Don’t forget to subscribe to our Power BI publication and Weekly Newsletter…

I hope this post helps! Let me know if you have any questions.
Hope to see you on the next story…

Check out my other articles:
🚀Direct Lake in Microsoft Fabric: A Revolutionary Feature for Data Storage and Analysis
📣Microsoft Fabric: Simplifying Complexity in the Data Analytics Process
📊How to Create an Organizational Hierarchy in Power BI?🔥