In this writing, I want to share when I try using INDEX DAX function in Power BI.
Recently (December 2022), there were three DAX functions released. Those are OFFSET, INDEX, and WINDOW DAX functions.
Until today (19th December 2022), I have not found much of blog posts or Youtube videos that describe use cases of INDEX DAX function. However, there are quite a lot talking about OFFSET and WINDOW DAX functions.
I like to take this opportunity to share when might be the situation for me to use INDEX DAX function in Power BI.
Before moving forward, I like to show the definition of INDEX DAX function that is written in the Microsoft website.
"Returns a row at an absolute position, specified by the position parameter, within the specified partition, sorted by the specified order. If the current partition can't be deduced to a single partition, multiple rows may be returned."
The word, absolute position, caught my interest, because in some scenarios, I wanted to know every Rank-No-1 revenue in every partition, and the number 1 in Rank No.1 is an absolute number.
Not a long time ago, I faced a situation to provide analysis report that compares current quarter's revenue VS. Rank No.1 Revenue of the quarter in the year.
The sample datamodel looks like below. And the sample pbix file is attached at the end of this blog post.
Before authoring a solution with INDEX DAX function, I usually wrote the solution like below.
First, I wrote [Rev rank by Quarter: ] measure.
Second, I wrote [Rev Quarter rank 1 by Q dax:] measure.
Rev rank by Quarter: = RANKX ( FILTER ( SUMMARIZE ( ALL ( 'Calendar' ), 'Calendar'[Year], 'Calendar'[Q-Year], 'Calendar'[End of Quarter] ), 'Calendar'[Year] = MAX ( 'Calendar'[Year] ) ), [Revenue:], , DESC )
Rev Quarter rank 1 by Q dax: = CALCULATE ( [Revenue:], FILTER ( ADDCOLUMNS ( FILTER ( ALL ( 'Calendar'[Q-Year], 'Calendar'[End of Quarter], 'Calendar'[Year] ), 'Calendar'[Year] = MAX ( 'Calendar'[Year] ) ), "@Rank", [Rev rank by Quarter:] ), [@Rank] = 1 ) )
There might be more efficient way, or even writing only one measure to get the correct solution. But let me try to compare the above with the new way of writing with using a new DAX function.
In the below figure, the table visualization with the same number is created without using RANKX DAX function, and with using INDEX DAX function.
Rev Quarter INDEX rank 1 by Q dax: = CALCULATE ( [Revenue:], INDEX ( 1, ADDCOLUMNS ( ALL ( 'Calendar'[Q-Year], 'Calendar'[End of Quarter], 'Calendar'[Year] ), "@Rev", [Revenue:] ), ORDERBY ( [@Rev], DESC ), KEEP, PARTITIONBY ( 'Calendar'[Year] ) ) )
In the above screen shot, there is one red line that indicates an error in the measure. However, it still showed an correct answer. I think this is a kind of bug and I hope this will be solved soon.
What I like In the second try (with using INDEX DAX function) are, INDEX DAX function gave a capability to ORDERBY the highest revenue to the lowest revenue. Furthermore, it also provided a partition where to renew the ORDERBY capability.
Also, comparing the two in DAX Studio, the differences are found like below.
without INDEX DAX function
with INDEX DAX function
To summarize, INDEX DAX function can combine the capability of ORDERBY and PARTIONBY parameters, and these enable to select an absolute position that is specified by a position parameter (sorted by the specified order) within a range that is defined by a partition parameter.
INDEX DAX function might be a handy-choice to use in some scenarios, like the above. I hope this helps to have more fun in manipulating parameters in INDEX DAX function.
Comments