Forum Discussion

snehasissamal's avatar
snehasissamal
Frequent Visitor
2 years ago
Solved

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 ...
  • Jihwan_Kim's avatar
    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]
                    )
                )
        )