Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi,
I have a table with different tasks as follows , I want to create 3 records for each task to track the efforts against 3 values(Estimated, Spent & Remaining) as follows. Any help to dynamically get the data using DAX in desired format is much appreaciated.
Original Table
Task ID | Title | Task Status | AssignedTo | EstimatedHours | CompletedHours |
1 | Task A | Open | Tom | 8 | 0
|
2 | Task B | In Proress | David | 24 | 10 |
3 | Task C | Open | Raj | 30 | 0 |
4 | Task DC | losed | Tom | 10 | 10 |
5 | Task E | Open | David | 12 | 0 |
Status
EffortStatus |
Assgined |
Spent |
Remaining |
Need data in below format,
Task ID | Title | Task Status | AssignedTo | EffortStatus | Hours |
1 | Task A | Open | Tom | Assgined | 8 |
1 | Task A | Open | Tom | Spent | 0 |
1 | Task A | Open | Tom | Remaining | 8 |
2 | Task B | In Proress | David | Assgined | 24 |
2 | Task B | In Proress | David | Spent | 10 |
2 | Task B | In Proress | David | Remaining | 14 |
Probably I have to create 3 rows with new column as " EffortStatus" first, and then loop and based on the "EffortStatus" value, I have to calculate the hours.
Solved! Go to Solution.
Hi,
I am not sure if I understood your question correctly, but I tried to creat a table like below.
Please check the below picture and the attached pbix file.
It is for creating a new table.
expected result table =
VAR _t =
SUMMARIZE (
'Original Table',
'Original Table'[Task ID],
'Original Table'[Title],
'Original Table'[Task Status],
'Original Table'[AssignedTo]
)
VAR _effortstatus = 'Status'
VAR _result =
GENERATE ( _t, _effortstatus )
RETURN
ADDCOLUMNS (
_result,
"Hours",
SWITCH (
TRUE (),
'Status'[EffortStatus] = "Assgined",
SUMX (
FILTER (
'Original Table',
'Original Table'[Task ID] = EARLIER ( 'Original Table'[Task ID] )
),
'Original Table'[EstimatedHours]
),
'Status'[EffortStatus] = "Spent",
SUMX (
FILTER (
'Original Table',
'Original Table'[Task ID] = EARLIER ( 'Original Table'[Task ID] )
),
'Original Table'[CompletedHours]
),
SUMX (
FILTER (
'Original Table',
'Original Table'[Task ID] = EARLIER ( 'Original Table'[Task ID] )
),
'Original Table'[EstimatedHours] - 'Original Table'[CompletedHours]
)
)
)
Hi @Jihwan_Kim ,
Thank you so much it worked as expected & have marked as accepted answer so that it may help opthers.
I was looking at the output, for different Tasks, Status column values are in dfferent order. For Example. for Task A, Status values are (Spent, Assigned, Remaining) while for TaskB, its (Assigned, Spent, Remaining) as in the picture.
Can we sort it or keep it in same order?
Hi,
I am not sure if I understood your question correctly, but I tried to creat a table like below.
Please check the below picture and the attached pbix file.
It is for creating a new table.
expected result table =
VAR _t =
SUMMARIZE (
'Original Table',
'Original Table'[Task ID],
'Original Table'[Title],
'Original Table'[Task Status],
'Original Table'[AssignedTo]
)
VAR _effortstatus = 'Status'
VAR _result =
GENERATE ( _t, _effortstatus )
RETURN
ADDCOLUMNS (
_result,
"Hours",
SWITCH (
TRUE (),
'Status'[EffortStatus] = "Assgined",
SUMX (
FILTER (
'Original Table',
'Original Table'[Task ID] = EARLIER ( 'Original Table'[Task ID] )
),
'Original Table'[EstimatedHours]
),
'Status'[EffortStatus] = "Spent",
SUMX (
FILTER (
'Original Table',
'Original Table'[Task ID] = EARLIER ( 'Original Table'[Task ID] )
),
'Original Table'[CompletedHours]
),
SUMX (
FILTER (
'Original Table',
'Original Table'[Task ID] = EARLIER ( 'Original Table'[Task ID] )
),
'Original Table'[EstimatedHours] - 'Original Table'[CompletedHours]
)
)
)
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
7 |
User | Count |
---|---|
14 | |
12 | |
11 | |
10 | |
9 |