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.
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())
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 |
---|---|
105 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
141 | |
107 | |
100 | |
82 | |
74 |