SUM of posted worker hours per department

[calculator]

Hello everybody,

I'm stuck with the following problem. I ran in circles for the whole week, read all the articles and forums solutions about tables with two dates (and userrelationship()) and LOOKUPVALUE and could not figure out the way to go.

First of all, the original report is using DirectQuery. That might limit the number of possible solutions dramatically.

Simplified, the data model is the following:

My goal is this:

For every Department  I need to sum up the hours that every employee worked for that department. In addition the calculation must have a date filter, so that I can lookup the posted hours only for a selected time period.

Here is the demo data: https://drive.google.com/file/d/1BKNwvCDCpW4vuraUTTglP6oOKdNAHBAl/view?usp=sharing

The best way (I could figure out) would be to extend the Postings table with a reference to Employment. Something like:

for every time posting:
    lookup in the employment table a match of
        Postings[PersonnelNumber] = Employment[PersonnelNumber]
        && Postings[Date] >= Employment[StartDate] 
        && Postings[Date] <= Employment[EndDate] 

With that I could make a relationship between Postings and Employment. And simply use SUMX

[Icey]

Hi @calculator ,

Please check:

1. Relationships:

2. Create a measure:

Measure =
VAR t =
    FILTER (
        CROSSJOIN ( Employment, Postings ),
        Employment[PersonnelNumber] = Postings[PersonellNumber]
            && Postings[Date] >= Employment[StartDate]
            && Postings[Date] <= Employment[EndDate]
    )
RETURN
    SUMX ( t, [HoursQuantity] )

3. Result:

Best Regards,

Icey

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

[calculator]

I tried the proposed solution, it makes totally sense and works quite well for the constructed data of my example.

But I get a very weird effect with my production data.

In the production data, the Postings table contains some more fields. One of these fields is the project-id. Our employees post their working time on projects. And since the managers of our departments always want to know where the numbers come from, I need to create some detail visuals.

So I created a matrix to show how the measure is broken down for the employees of the department and the projects they worked for.

This leads to the following effect:

In the top matrix the PersonnelNumber is taken from the Worker table. But the same effect happens with Worker[Name] or Employment[PersonnelNumber]. As you can see the measure (sumx uppon the crossjoin) and the count for the personnel number is wrong. But the drilldown (the projects-IDs) and the total sum ("Gesamt") show correct values.

In the bottom matrix the PersonnelNumber is taken from the Postings table. Here all the numbers are correct.

Since I have no clue where this may come from, I even cannot provide some more example data.

Because the department managers don't know their employees by personnel-number, the visuals need to use the name field. But as I described above, the effect happens with every field from the Worker table.

[Icey]

Hi @calculator ,

I can't reproduce your issue.

Please try to change the direction of the relationships to "Both".

Best Regards,

Icey

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

[calculator]

I found the following within the CROSSJOIN documentation:

---

This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

---

As I described in the initial post, I have to use DirectQuery. And the PersonnelNumber field in the Employment table is a calculated column from a side table.

So the current effect is, that the CROSSJOIN misses some lines from the Postings table. Can somebody explain, what not supported means in that context? Do I have any other option other than using CROSSJOIN? Is it possible to create a calculated column Employment into the Postings table, so I could connect the Postings table directly with the Employment table?

[calculator]

Hello @Icey ,

Thank you very much for your reply. 

I will need some time to analyse your suggestion with my production data.