Create multiple rows against unique Tasks using DAX

[snehasissamal]

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.

[snehasissamal]

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?

[Jihwan_Kim · Accepted 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]
                )
            )
    )