Forum Discussion
Help expanding table according dates
I have a table like below, where a new record is created when there is a change in the status of a task.
| task | stat | lastupdate |
| A | 1 | 28/04/2022 |
| A | 3 | 01/05/2022 |
| A | 5 | 05/05/2022 |
| B | 1 | 28/04/2022 |
| B | 3 | 03/05/2022 |
| B | 4 | 05/05/2022 |
The problem is that I need to plot a graph within a time range, where I know the status of each item regardless of the date it was changed/created. With that, I think the easiest is to transform to the table below:
| task | status | lastupdate |
| A | 1 | 28/04/2022 |
| A | 1 | 29/04/2022 |
| A | 1 | 28/04/2022 |
| A | 1 | 29/04/2022 |
| A | 1 | 30/04/2022 |
| A | 3 | 01/05/2022 |
| A | 3 | 02/05/2022 |
| A | 3 | 03/05/2022 |
| A | 3 | 04/05/2022 |
| A | 5 | 05/05/2022 |
| B | 1 | 28/04/2022 |
| B | 1 | 29/04/2022 |
| B | 1 | 30/04/2022 |
| B | 1 | 01/05/2022 |
| B | 1 | 02/05/2022 |
| B | 3 | 03/05/2022 |
| B | 3 | 04/05/2022 |
| B | 4 | 05/05/2022 |
However, I can't think of a way to do it, either directly in Power BI or even in SQL, since I'm connecting to a redshift database through a sql query. Could you please help me? Thank you
Dukezera Try:
Tasks2 = ADDCOLUMNS( GENERATE( DISTINCT('Tasks'[task]), CALENDAR(MIN('Tasks'[lastupdate]),MAX('Tasks'[lastupdate])) ), "status", VAR __Date = [Date] VAR __Task = [task] VAR __TargetDate = MAXX(FILTER('Tasks',[lastupdate] <= __Date && [task] = __Task),[lastupdate]) RETURN MAXX(FILTER('Tasks',[task] = __Task && [lastupdate] = __TargetDate),[stat]) )Dukezera Great! So I don't know where those columns are coming from exactly or how they work but seems like you could do something like:
Tasks2 = VAR __Table = ADDCOLUMNS( GENERATE( DISTINCT('Tasks'[task]), CALENDAR(MIN('Tasks'[lastupdate]),MAX('Tasks'[lastupdate])) ), "status", VAR __Date = [Date] VAR __Task = [task] VAR __TargetDate = MAXX(FILTER('Tasks',[lastupdate] <= __Date && [task] = __Task),[lastupdate]) RETURN MAXX(FILTER('Tasks',[task] = __Task && [lastupdate] = __TargetDate),[stat]), "approved", VAR __Date = [Date] VAR __Task = [task] VAR __TargetDate = MAXX(FILTER('Tasks',[lastupdate] <= __Date && [task] = __Task),[lastupdate]) RETURN MAXX(FILTER('Tasks',[task] = __Task && [lastupdate] = __TargetDate),[approved]), "stn", VAR __Date = [Date] VAR __Task = [task] VAR __TargetDate = MAXX(FILTER('Tasks',[lastupdate] <= __Date && [task] = __Task),[lastupdate]) RETURN MAXX(FILTER('Tasks',[task] = __Task && [lastupdate] = __TargetDate),[stn]) ) RETURN FILTER(__Table, [status] <> BLANK() && [approved] <> BLANK() && [stn] <> BLANK())
5 Replies
Dukezera Try:
Tasks2 = ADDCOLUMNS( GENERATE( DISTINCT('Tasks'[task]), CALENDAR(MIN('Tasks'[lastupdate]),MAX('Tasks'[lastupdate])) ), "status", VAR __Date = [Date] VAR __Task = [task] VAR __TargetDate = MAXX(FILTER('Tasks',[lastupdate] <= __Date && [task] = __Task),[lastupdate]) RETURN MAXX(FILTER('Tasks',[task] = __Task && [lastupdate] = __TargetDate),[stat]) )I tried but got this error:
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.Dukezera What I provided is a calculated table formula.
