Sometimes, when you create advanced calculations in DAX, you need to create an auxiliary index table which contains increasing number like in the example below.
There are several ways one can approach this problem. Marco Russo described some of them for the general case of creating static tables in his article . Here I would like to present a different approach which works great for the more specific problem of index tables.
Create a table with one column where rows are increasing from 1 to 5.
In Power BI we can create such table with Power Query or directly in DAX using calculated tables. In the first approach we would use
Second, is more challenging as there is no such function in DAX.
Luckily, there is
CALENDAR function which can be used to generate a table!
IndexTable = VAR Length = 5 VAR DummyDate = DATE ( 2000, 1, 1 ) RETURN CALENDAR ( DummyDate, DummyDate + Length )
Now we just need to clean it up by converting dates to numbers, adding new column, and removing the original column .
IndexTable = VAR Length = 5 VAR DummyDate = DATE ( 2000, 1, 1 ) RETURN FILTER ( SUMMARIZE ( ADDCOLUMNS ( CALENDAR ( DummyDate, DummyDate + Length ), "Index", INT ( [Date] ) - INT ( DummyDate ) ), [Index] ), [Index] > 0 )