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.
Index |
---|
1 |
2 |
... |
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 [1]. Here I would like to present a different approach which works great for the more specific problem of index tables.
Problem statement
Create a table with one column where rows are increasing from 1 to 5.
Index |
---|
1 |
2 |
3 |
4 |
5 |
Solution
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 List.Numbers
function.
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 )
Date |
---|
2000-01-01 00:00:00 |
2000-01-02 00:00:00 |
2000-01-03 00:00:00 |
2000-01-04 00:00:00 |
2000-01-05 00:00:00 |
Now we just need to clean it up by converting dates to numbers, adding new column, and removing the original column [4].
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
)
Brak komentarzy:
Prześlij komentarz