Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |