Create multiple rows against unique Tasks using DAX

Question

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 &amp; Remaining) as follows. Any help to dynamically get the data using DAX in desired format is much appreaciated.&nbsp;Original TableTask IDTitleTask StatusAssignedToEstimatedHoursCompletedHours1Task AOpenTom80&nbsp;2Task BIn ProressDavid24103Task COpenRaj3004Task DClosedTom10105Task EOpenDavid120&nbsp;Status&nbsp;EffortStatusAssginedSpentRemaining&nbsp;Need data in below format,&nbsp; &nbsp;Task IDTitleTask StatusAssignedToEffortStatusHours1Task AOpenTomAssgined81Task AOpenTomSpent01Task AOpenTomRemaining82Task BIn ProressDavidAssgined242Task BIn ProressDavidSpent102Task BIn ProressDavidRemaining14&nbsp;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.&nbsp;

jihwan_kim · Accepted Answer

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.
&nbsp;

&nbsp;
&nbsp;
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]
                )
            )
    )

Forum Discussion

Create multiple rows against unique Tasks using DAX

2 Replies

Related content

Creating multiple Audiences in PowerBI App

Multiple messages when new item created

Creating Multiple manage relationships at ones

How to avoid people creating multiple times the same table in different workspaces

MULTIPLE CARD

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