Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I trying to establish a time series data set to serve as input for a line chart in DAX.
Below is a sample Projects table of my starting point.
ProjectID | TimeRegistrationDateMin | StatusReportDateMax |
1 | 01-01-2020 | 05-01-2020 |
2 | 10-01-2020 | 13-01-2020 |
3 | 20-01-2020 | 27-01-2020 |
To ensure all dates between the [TimeRegistrationDateMin] and [StatusReportDateMax] are available as row entries in the to-be table, I have tried using the GENERATESERIES() function. However, I cannot get my head around how to loop over each project of the Projects table, to produce a time series for each project in the same output table.
Desired output is as follows:
ProjectID | Date |
1 | 01.10.2020 |
1 | 02.10.2020 |
1 | 03.10.2020 |
1 | 04-01-2020 |
1 | 05-01-2020 |
2 | 10-01-2020 |
2 | 11-01-2020 |
2 | 12-01-2020 |
2 | 13-01-2020 |
3 | 20-01-2020 |
3 | 21-01-2020 |
3 | 22-01-2020 |
3 | 23-01-2020 |
3 | 24-01-2020 |
3 | 25-01-2020 |
3 | 26-01-2020 |
3 | 27-01-2020 |
In PQ, I have managed to to this by adding a custom column containing a list between the two dates for each project, and the expanded the lists of the column. However, due to performance, this needs to be computed in DAX.
Thanks in advance for your time and input!
Hi @Anonymous -
You can use the GENERATE function
TimeSeries =
GENERATE (
Project,
FILTER (
ALL ( DateTab[Date] ),
DateTab[Date] <= Project[StatusReportDateMax]
&& DateTab[Date] >= Project[TimeRegistrationDateMin ]
)
)
Hope this helps
David
David, where should I paste the "GENERATE" code? Should it be pasted in New Query?
No, you will put it in a "New Table" DAX formula.
User | Count |
---|---|
54 | |
22 | |
18 | |
16 | |
11 |
User | Count |
---|---|
80 | |
57 | |
40 | |
19 | |
10 |