Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Thomas_D
Regular Visitor

Calculating amount of working hours in another table and define the filter on the result

Hi Community,
I spent already a lot of time to resolve this, but I don't find the right approach for that.

Task:

I have to calculate the headcount of our company corresponding to the date range which is selected in the report by a slicer.

Situation:
I have a table let's call it "Employee" which have two relevant dates. The start date and the end date of the emploment. 
Additional  I have already a calendar table which is used for the filtering of the dates.  
What already works correctly, is to calculate the employees based on the start and end date.

What I need now is an additional filter, so that "external" employess are will take into account only if they have worked more than 20 hours in a month.  Therefore we have a table with the data of the working time measurement.  
I trired some different approaches but I didn't find the correct one yet.  I didn't manage it to calcualte the monthly working time for the Externals in the right way. 

Here is my measure what works in the case of taking the employment dates into account

MAH Extern = CALCULATE( COUNTROWS(Employee)),

FILTER (Employee, Employee[Int/Ext] = "extern")
,FILTER (Employee, Employee[Startdate] <= MAX(Calendar[Date]))
,FILTER ('Employee', ISBLANK(Employee[Enddate]) || Employee[Enddate] > MIN(Calendar[Date])
))

e.g. i've tried this approach, but there is someting with the filter context not correct as it returns the exact same numbers as the measure above:

MAHeadcountExternValidWithWork_NEW2 =

MAH Extern = CALCULATE( COUNTROWS(Employee)),

FILTER (Employee, Employee[Int/Ext] = "extern")
,FILTER (Employee, Employee[Startdate] <= MAX(Calendar[Date]))
,FILTER (Employee, ISBLANK(Employee[Enddate]) || Employee[Enddate] > MIN(Calendar[Date])
,FILTER (WorkingHours, SUM(WorkingHours[Hours]) > 20))
))

How do I need to sum the monthly working houres in this context correctly.

Thanks
Thomas

2 REPLIES 2
Thomas_D
Regular Visitor

Thanks for that which solved my first problem.
I've an additional problem which is caused in any way by the filter context. I tried some different approaches to use different filter criterias but I could not find the right solution.

As described above for my first measure the start date for an employment is relevant. 
I use the date column from the calendar table in the data model. On the report I have a date slicer which defines the start and end date of the relevant time period.
In the first step I only want to use the start date of an employment. When I create a measure where the date is hare coded everything looks fine. But when I use the min or max date out of the date column of my calendar table something goes wrong.

In the following screen shot you see two cards with the values I get when I use Min(Calendar[Date]) Max(Calendar[Date]). 
In the table you see two measures. The left one (where date is hard coded) counts correctly, the right column is wrong.

Thomas_D_0-1619525849749.png

Here is the syntax of the two measures:

Measure 1:

MAH Extern EXT und Eintritt FILTER HARD = 
var Result = 
    CALCULATE(
        COUNTROWS('Mitarbeiter (BlueAnt)'),
        FILTER('Mitarbeiter (BlueAnt)', 'Mitarbeiter (BlueAnt)'[Int/Ext] = "extern")
        ,FILTER(ALL('Mitarbeiter (BlueAnt)'), 'Mitarbeiter (BlueAnt)'[Eintrittsdatum] <= DATE(2020,01,31))
        )
return Result

 

Measure 2:
MAH Extern EXT und Eintritt FILTER CAL = 
var MinDate = MIN(Kalender[Datum])
var MaxDate = MAX(Kalender[Datum])

var Result = 
    CALCULATE(
        COUNTROWS('Mitarbeiter (BlueAnt)'),
        FILTER('Mitarbeiter (BlueAnt)', 'Mitarbeiter (BlueAnt)'[Int/Ext] = "extern")
        ,FILTER('Mitarbeiter (BlueAnt)', 'Mitarbeiter (BlueAnt)'[Eintrittsdatum] <= MaxDate)
        )
return Result



Within the data model, the calendar table has a relation to the table with the saved working hours. The working hour table has a realtion to my employee table. 

What did I miss? 





Anonymous
Not applicable

 

MAH Extern =
var MinimumDate = MIN( Calendar[Date] )
var MaximumDate = MAX( Calendar[Date] )
var Result =
    CALCULATE(
        COUNTROWS( Employee ),
        KEEPFILTERS(
            Employee[Int/Ext] = "extern"
        ),
        KEEPFILTERS(
            Employee[Startdate] <= MaximumDate
        ),
        KEEPFILTERS(
            OR(
                ISBLANK( Employee[Enddate] ),
                Employee[Enddate] > MinimumDate
            )
        )
    )
return
    Result


// 2nd measure (can be hidden if you don't need it)
[Total Working Hours] = SUM( WorkingHours[Hours] )


MAHeadcountExternValidWithWork =
CALCULATE(  
    [MAX Extern],
    FILTER(
        DISTINCT( Employee[EmployeeID] ),
        [Total Working Hours] > 20
    )
)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors