cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
calculator
Regular Visitor

SUM of posted worker hours per department

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:

screenshot_2020-09-18_11-07-49.png

 

My goal is this:
screenshot_2020-09-18_11-12-24.png

 

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

5 REPLIES 5
Icey
Community Support
Community Support

Hi @calculator ,

 

Please check:

 

1. Relationships:

relation.PNG

 

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:

filter.gif

 

 

 

Best Regards,

Icey

 

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

 

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:

screenshot_2020-09-24_11-09-39.png

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
Community Support
Community Support

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.

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?

Hello @Icey ,

 

Thank you very much for your reply. 

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors