Measure to apply a weekly distinct count based on a status on the last day of the week

[Niams93]

Hello ! 

I'm here to look for help with a  measure that's giving me quite the headache because of a subtility in the data.

Here is the case : 

I've got 3 tables :

- a Dimension table 'Table - Employees' 

- a Fact Table 'Table - Employees Activity' 

- a Calendar Table 'Table - Calendar'

Relationships : 

'Table - Employees' is related to 'Table - Employees Activity' thanks to the fields 'Table - Employees'[ID Employee] and 'Table - Employees Activity'[ID Employee]. 

'Table - Employees Activity' is linked to 'Table - Calendar' via the fields 'Table - Employees Activity'[ID CalendarDate] and 'Table - Calendar'[Date Int]. 

The relationships are well defined, as this dataset was already functional without the measure I'm trying to add and it doesn't use any new source.  

Business case : 

I have to create a measure that lets me know the number of distinct part-timer employees who worked more than 35 hours a week.

I have the following informations for this calculation :

'Table - Calendar'[Week Number ISO] -> Number of the week in the year selected

'Table - Employees Activity'[Effective Working Time in hours] ->  Amount of hours worked by an employee

'Table - Employees Activity'[Is Full Time] ->  A Boolean that lets me know if an employee is a full time worker or a part-timer.

'Table - Calendar'[Day Of Week] -> Get the number of the day of the week (where 1 = Monday and 7 = Sunday)

'Table - Employees'[Contract Type] -> Used to add a filter (if the contract type begins by a certain character chain, the row must be excluded from the calculation)

And other fields which I also use in the measure but are less relevant for this case. 

The subtility here is that some part-timer employees ('Table - Employees Activity'[Is Full Time] = TRUE) can turn to full time employees ('Table - Employees Activity'[Is Full Time] = FALSE) during the same week.

To handle this case, we've decided to only take into account the status of an employee in the last day of the week, so Sunday. For instance, , if an Employee was full time from Monday to Friday and then became a part timer for the Saturday and Sunday of the same week, he'll be considered as a part-timer for the this whole week.

The goal here is to display a visual table using the fields 'Table - Employees'[Employee Code], 'Table - Calendar'[Week Number ISO] and the measure to know for each week how many distinct part timers worked more than 35 hours.

Constraints :

To respect the Business' will : 

- the calculation must be done in a measure 

-Power Query can't be used 

-No calculated columns can be created 

What I've tried :

I don't have a lot of experience in DAX but here are my attempts.

This first one seems to works at first glance  but doesn't take into account the part-timer's switch of status :

// Calculate the number of weekly hours worked by the employees
VAR _WeeklyHours =
    ADDCOLUMNS (
        SUMMARIZE (
            'Table - Employees Activity',
            'Table - Calendar'[Week Number ISO],
            'Table - Employees Activity'[ID Employee],
            'Table - Employees'[Employee Code],
            "TotalHours",
                SUM ( 'Table - Employees Activity'[Effective Working Time in hours] )
        ),
        "IsFullTime",
            CALCULATE ( SELECTEDVALUE ( 'Table - Employees Activity'[Is Full Time] ) ),
        "IsFlatDay",
            CALCULATE ( SELECTEDVALUE ( 'Table - Employees Activity'[Is Flat day] ) ),
        "ContractType",
            LOOKUPVALUE (
                'Table - Employees'[English Contract Type],
                'Table - Employees'[ID Employee],
                'Table - Employees Activity'[ID Employee]
            )
    )

// Filter the employees
VAR _CollaboratorsOver35Hours =
    FILTER (
        _WeeklyHours,
            [TotalHours] > 35
            && [IsFullTime] = FALSE
            && [IsFlatDay] = FALSE
            && NOT ( ISBLANK ( [Employee Code] ) )
            && LEFT ( [ContractType], 3 ) <> "CTT"
    )

// Apply a distinct count of the filtered employees

VAR _DistinctEmployeeCodes =
    DISTINCT (
        SELECTCOLUMNS (
            _CollaboratorsOver35Hours,
            "EmployeeCode", [Employee Code]
        )
    )

// Return the result
RETURN
    COUNTROWS ( _DistinctEmployeeCodes )

This second one adds the Sunday "workaround" but doesn't solve the issue :

// Calculate the number of weekly hours worked by the employees
VAR _WeeklyHours =
    ADDCOLUMNS (
        SUMMARIZE (
            'Table - Employees Activity',
            'Table - Calendar'[Week Number ISO],
            'Table - Employees Activity'[ID Employee],
            'Table - Employees'[Employee Code],
            "TotalHours",
                SUM ( 'Table - Employees Activity'[Effective Working Time in hours] )
        ),
        // Get the "Is Full Time" value on the Sunday of the week selected

         "IsFullTimeSunday",
            CALCULATE (
                SELECTEDVALUE ( 'Table - Employees Activity'[Is Full Time] ),
                'Table - Calendar'[Day Of Week] = 7
            ),
        "IsFlatDay",
            CALCULATE ( SELECTEDVALUE ( 'Table - Employees Activity'[Is Flat day] ) ),
        "ContractType",
            LOOKUPVALUE (
                'Table - Employees'[Contract Type],
                'Table - Employees'[ID Employee],
                'Table - Employees Activity'[ID Employee]
            )
    )

// Filter the employees
VAR _CollaboratorsOver35Hours =
    FILTER (
        _WeeklyHours,
            [TotalHours] > 35
            && [IsFullTimeSunday] = FALSE
            && [IsFlatDay] = FALSE
            && NOT ( ISBLANK ( [Employee Code] ) )
            && LEFT ( [ContractType], 3 ) <> "CTT"
    )

// Apply a distinct count of the filtered employees
VAR _DistinctEmployeeCodes =
    DISTINCT (
        SELECTCOLUMNS (
            _CollaboratorsOver35Hours,
            "EmployeeCode", [Employee Code]
        )
    )

// Return the result
RETURN
    COUNTROWS ( _DistinctEmployeeCodes )

I'm sorry I can't share the dataset (I even anonymized the values in the DAX I shared), if you need more details, I'll try my best to give you more details.
Thank you in advance ! 

[V-yubandi-msft]

Hi @Niams93 ,

Thank you for engaging with the Microsoft Fabric Community. I tested the @johnt75  measure using mock data, and the logic is functioning as expected. It accurately identifies part time employees whose total hours exceed 35, based on their latest employment status.

The final count aligns with your scenario, and the table breakdown supports this. This approach should effectively highlight part time employees with unusually high hours.
FYI:

For your reference, I’ve attached the PBIX file. Please feel free to review it, and let me know if you need any additional details to proceed.

Thanks for your response @johnt75 .

Regards,
Yugandhar.

[Niams93]

Hi, indeed the solution works perfectly fine ! Thank you for the example in the pbix  

[johnt75 · Accepted solution]

You can try

Num part time more than 35 hours =
VAR EmployeeWithLastStatus =
    INDEX (
        1,
        SUMMARIZE (
            'Table - Employees Activity',
            'Table - Employees Activity'[ID Employee],
            'Table - Calendar'[Day Of Week],
            'Table - Employees Activity'[Is Full Time]
        ),
        ORDERBY ( 'Table - Calendar'[Day Of Week], DESC ),
        PARTITIONBY ( 'Table - Employees Activity'[ID Employee] )
    )
VAR PartTimeEmployees =
    SELECTCOLUMNS (
        FILTER (
            EmployeeWithLastStatus,
            'Table - Employees Activity'[Is Full Time] = FALSE
        ),
        'Table - Employees Activity'[ID Employee]
    )
VAR PartTimeEmployeesWithHours =
    ADDCOLUMNS (
        PartTimeEmployees,
        "@num hours",
            CALCULATE (
                SUM ( 'Table - Employees Activity'[Effective Working Time in hours] )
            )
    )
VAR Result =
    COUNTROWS ( FILTER ( PartTimeEmployeesWithHours, [@num hours] > 35 ) )
RETURN
    Result

This will get the last part time / full time status for each employee in the week, regardless of which day it is. This would successfully return a value even for employees who didn't have an entry for the Sunday in a given week.

[Niams93]

Thank you very much for your solution @johnt75, It works perfectly fine ! 

If I may ask one last this on this subject : from what I've understood, the distinct count in your solution seems handled via the INDEX and PARTITION BY which let me have one row per employee. If I wanted to duplicate this measure and to create one which does the same but that removes the "distinct" part of the count, what would be the best approach ? 

[johnt75]

If I understand you correctly you want to to use a similar technique to get the latest status for each ID, apply a filter to the list of IDs based on some condition and then use the remaining IDs to e.g. count the number of rows in a table.

If my understanding is correct, then you can reuse the code up to the Result part. The IDs in the PartTimeEmployeesWithHours variable still has the correct lineage to be able to filter the model, so you can use that variable, or a filtered version of it, as a filter. e.g. To count the number of rows in the employee activity table you could use something like

VAR FilteredEmployees =
    FILTER ( PartTimeEmployeesWithHours, [@num hours] > 35 )
VAR Result =
    CALCULATE ( COUNTROWS ( 'Table - Employees Activity' ), FilteredEmployees )
RETURN
    Result

If you wanted to move the filter to the employees table rather than the employee activity table you could use SELECTCOLUMNS on the filtered PartTimeEmployeesWithHours variable to get just the IDs and then use TREATAS to treat those IDs as being from the employee table.

[Niams93]

Thank you for your suggestion, however that's not exactly what I meant by "non distinct", my bad I'll try to give you a clearer explanation of what I have in mind :

Let's say that we're focusing on the 2 first weeks of 2025.
In week 01, Employee N°01 worked 36 hours, Employee N°02 worked 37 hours and Employee N°03 worked 30 hours.
In week 02, Employee N°01 worked 37 hours, Employee N°02 worked 38 hours and Employee N°03 worked 42 hours.
By using the measure you've provided, for these two weeks, the result will return 3 because the count is distinct (it will count Employee 01 & 02 from week N°01 and Employee 03 from week N°02, since they worked > 35 hours in the week).
For the variant I wanted to create, I would like to apply the same logic as your measure (part time employees who worked > 35 hours a week and taking into account the last status of their contract on the Sunday of the week) but by "removing" the notion of distinct.
In the example above, the variant measure I want to create would return 5 instead of 3 (Employee 01 and 02 would be counted twice, one time for each week where they worked more than 35 hours)

[johnt75]

I think you can achieve that by adding the week number into the code like

Num part time more than 35 hours =
VAR EmployeeWithLastStatus =
    INDEX (
        1,
        SUMMARIZE (
            'Table - Employees Activity',
            'Table - Employees Activity'[ID Employee],
            'Table - Calendar'[Week Number ISO],
            'Table - Calendar'[Day Of Week],
            'Table - Employees Activity'[Is Full Time]
        ),
        ORDERBY ( 'Table - Calendar'[Day Of Week], DESC ),
        PARTITIONBY (
            'Table - Employees Activity'[ID Employee],
            'Table - Calendar'[Week Number ISO]
        )
    )
VAR PartTimeEmployees =
    SELECTCOLUMNS (
        FILTER (
            EmployeeWithLastStatus,
            'Table - Employees Activity'[Is Full Time] = FALSE
        ),
        'Table - Employees Activity'[ID Employee],
        'Table - Calendar'[Week Number ISO]
    )
VAR PartTimeEmployeesWithHours =
    ADDCOLUMNS (
        PartTimeEmployees,
        "@num hours",
            CALCULATE (
                SUM ( 'Table - Employees Activity'[Effective Working Time in hours] )
            )
    )
VAR Result =
    COUNTROWS ( FILTER ( PartTimeEmployeesWithHours, [@num hours] > 35 ) )
RETURN
    Result

This version of the measure should work no matter what granularity you have on the date axis, i.e. if you plot by week on the chart then week 1 would show 2 and week 2 would show 3, but if you combine both weeks it would show 5.

[Niams93]

Ah perfect that's the result I was looking for, thank you so much again for your time and your help ! 

Have a nice day

[FBergamaschi]

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. Alternatively, you can share your pbix via some cloud service and paste the link here. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here