Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
@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())
@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.
@Greg_Deckler works perfectly!!!!!!!
What do I need to do with I have a table like this (with two more column and works like status) and also how to remove rows with blank values in any of this columns (status, approved or stn)?:
task | status | lastupdate | approved | stn |
A | 1 | 28/04/2022 | N | N31 |
A | 3 | 01/05/2022 | N | N31 |
A | 5 | 05/05/2022 | Y | N31 |
B | 1 | 28/04/2022 | N | N31 |
B | 3 | 03/05/2022 | Y | N31 |
B | 4 | 05/05/2022 | Y | N31 |
@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())
User | Count |
---|---|
98 | |
90 | |
78 | |
72 | |
65 |
User | Count |
---|---|
114 | |
98 | |
96 | |
68 | |
67 |