Forum Discussion

kate1212klim's avatar
kate1212klim
New Member
3 years ago

How to rewrite values in columns using DAX

Hi community!

 

I'm new to power bi and would appreciate your help. 

I have a report with names, tasks, dates and hours spent on tasks.

The problem is to identify the task ABCD in each name and if there are such tasks I have to sum up the hours. Then I have to count all the other rows within each name and split the hours the name spent on ABCD task into the count of rows except ABCD. Then I have to increment each row within the name and date except where ABCD is into this number and rows with ABCD must remain empty or equal zero. In short I have to add hours spent by a name  within each month equally to other tasks. I tried creating different measures with the help of GPT but never reached my goal:

SUM of ABCD:

ABCD Sum = CALCULATE(
    [Total hours],
        FILTER(
            Table,
            Table[Employee] = [Employee]
                && (Table[Task] = "ABCD")
        )
    )

Row count:

Rowcount = 

    SUMMARIZE(
        FILTER(
            Table,
            Table[Employee] = [Employee] &&
            Table[Task] <> "ABCD"
        ),
        "Rowcount",
        COUNTX(Table, Table[Task]
    ))

Splitting hours:

Hrs split = 

DIVIDE([ABCD Sum], [Rowcount])

Adding to other rows except where ABCD is:

Added = 
SUMX(
    Filter(
        Table,
        Table[Employee] = [Employee] &&
        Table[Task] <> "ABCD"
        ),
    [Hrs split]
)

and it stopped working here - shows no data at all:(

The table itself is here 

 

Would appreciate your advice on how to solve my problem

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi kate1212klim ,

     

    You could create 2 what-if parameters for the 2 values of monthly changing.

     

     

    Then I get the slicer, which I chose in the "vertical list" format for both.

    Create the [hrs to add] measure and the [RESULT] measure.

    hrs to add = IF(MAX('Table'[Task])="ABCD",0,IF(MAX('Table'[Employee])="JLO",[JLO&ABCD Value],IF(MAX('Table'[Employee])="LGAGA",[LGAGA&ABCD Value])))
    RESULT = 
 var _res=[hrs to add]+COUNT('Table'[Hours])
 return 
 IF(MAX('Table'[Task])="ABCD",0,IF(MAX('Table'[Employee])="JLO",_res,IF(MAX('Table'[Employee])="LGAGA",_res
)))

                                                                                                                                                             

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.