Forum Discussion
snehasissamal
2 years agoFrequent Visitor
Create multiple rows against unique Tasks using DAX
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 ...
- 2 years ago
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] ) ) )
Jihwan_Kim
2 years agoSuper User
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]
)
)
)