cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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.

Hello @Icey ,

 

Thank you very much for your reply. 

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

 

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.

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?

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors