Calculate Planned and Worked hours by employee and activity

[luandrad]

Hi guys,

PBIX file: https://mega.nz

I'm trying show in the same visual table, the SUM of Planned and Worked hours (type INT e.g.: 08, 25) by employee and activity, but for each Month Date and Employee, is showwing all the activitys from dProjects as 0. In my model view, on visual "Table1" you will see what I'm saying.

The expected visual result that I'm looking for:

we have 4 combinations that could happen:

1. Planned = 0 / Worked = 0 (no planned activitys and no register of work hours)
2. Planned = 168 / Worked = 0 (planned to work in some activity but not register the hours as worked yet)
3. Planned = 0 / Worked = 168 (not planned to work but register hours as worked)
4. Planned = 168 / Worked = 168 (all's good! planned to work and register the worked hours for the activity)

When both measures of Hours been zero (0), the fields of project data should be blank.

Some one could try help me?

I don't know if my model data is the best to achive this result, but if we can solve this if DAX in each measure, would be great.

BR.

Lucio

[sturlaws]

Hi @luandrad 

I have had a look at your file, and read through your post a couple of times, but I don't understand what you are having problems with. Would you care to explain what you are having trouble with?

Cheers,
Sturla

[luandrad]

Hello @sturlaws , thanks for your reply.

The problem is that I need analyse for each month/employee (Table 1 for tests), if the employee has Planned or Worked hours in some activities, but in my current model, for each activity, are showing 0 for all (dProjects registers) for each employee.

In this image for "Funcionario 1 EFETIVO", just the highlighted values (168 / 168) are right because this employee has these SUM hours in both fact tables for this month (Jun-2020):

But, for the other ACTIVE employees ("FUNCIONARIO 2"), as he don't have values in fact tables, all the colouns values (from dProject) should be blank, and the measures (planned / worled) should show just (0 / 0), in one single row by each employee.

My visual "Tabela 2" has the value in the measures right for each employee context, but if I add the values of dProject (Table 1), the table will show extra wrong data.

I hope now I had provided a better explanation.

BR.

Lúcio

[sturlaws · Accepted solution]

Ok, I think I got it.

I have a solution for you, but it is not quite straight forward. 

First you have to add a dummy row to dProjetos. On this row set 'dProjetos'[ID_ACTIVITY]=-1, leave all other fields blank.

Next change your measure to this:

Planned Hours =
VAR _aID =
    CALCULATE ( MIN ( dProjetos[ID_ACTIVITY] ) )
VAR _tmpSum =
    CALCULATE ( SUM ( f_Planejamento_Det[Planned Hours] ), ALL ( dProjetos ) )
RETURN
    SUM ( f_Planejamento_Det[Planned Hours] )
        + IF ( _aID = -1 && ISBLANK ( _tmpSum ), 0, BLANK () )

and see if that does the trick.

And do the same thing to the worked hours and f_Apontamentos.

Cheers,
Sturla

[luandrad]

Thank you @sturlaws ,

Your solution atends whats I need! 🙂

I just need try let this one a litthe bit more perfomatic, because I had arround 1000 active employess to be analyse, so If don't have any filter in the page by employee and month, your original measure do not finish the DAX query (There's not enough memory").

If I add the conditional to check if is active as below, the table takes 65s:

Do you have any ideia to improve your measure? or may creat a DAX table with these data equal to my table1 visual?

BR.

Lúcio

[sturlaws]

The reason it takes so long, is that when I try to change the measure to return 0 for workers where there are no projects, the expression is evaluated for every combination of worker and project/activity. For your sample file it runs fine, but for large numbers of workers and/or projects, Power BI will run out of memory. 

I am struggeling to find a workaround, but could you try to set the relationship between dProjetos and the two fact tables to inactive and write the measures like this

Worked Hours =
VAR _tmp =
    CALCULATE (
        SUM ( f_Apontamentos[Register Hours] ),
        USERELATIONSHIP ( dProjetos[ID_ACTIVITY], f_Apontamentos[PKNI_ACTIVITY_ID] )
    )
RETURN
    IF (
        ISBLANK ( SUM ( f_Apontamentos[Register Hours] ) )
            && CALCULATE ( SELECTEDVALUE ( dProjetos[ID_ACTIVITY] ) ) = -1,
        0,
        _tmp
    )

(still with the dummy row in dProjetos)