Can you use a USERELATIONSHIP for two dates?

[DynamicsHS]

Hi, 

I want to show a table with active employees based on the year. Someone provided me with a userelationship measure to get the terminated employees to show. 

e.g.

Terminated within =

CALCULATE (

COUNTROWS ( cdm_worker ),

USERELATIONSHIP ( 'Date'[Date], cdm_worker[cdm_worker (2).End Date] )

)

I was wondering if it was possible to do the same but with the employees start and end date so that the table only shows employees who were active during that year? 

Thanks

[v-yiruan-msft]

Hi  @DynamicsHS ,

Is there any relationship between the table Date and cdm_worker? If yes, could you please provide the relationship info(Relationship field,Cardinality, Cross filter direction, active or inactive etc.)? Assume that you have the data model as below screenshot, you can create a measure as below to get the number of active employees.

Terminated within =
VAR _seldate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( cdm_worker[Worker ID] ),
        FILTER (
            'cdm_worker',
            'cdm_worker'[cdm_worker (2).Start Date] <= _seldate
                && OR (
                    'cdm_worker'[cdm_worker (2).End Date] >= _seldate,
                    ISBLANK ( 'cdm_worker'[cdm_worker (2).End Date] )
                )
        )
    )

In addition, you can refer the following links to get it:

How Many Staff Do We Currently Have – Multiple Dates Logic In Power BI Using DAX

Create inactive relationships

Create measure

Create visuals

Count Amount of Active Employees by period

Counting “Active” Rows in a Time Period: Guest Post from Chris Campbell

Best Regards

[DynamicsHS]

Hi @v-yiruan-msft & @tamerj1 ,

Currently I have multiple relationships running from the cdmworker table to the date table. More specifically, a relationship for the start and end date of the employees:

I am currently using this bar chart:

Which is pulling 3 dax measures:

Count of Active Employee - female = 
VAR endOfPeriod = MAX ( 'Date'[Date] )

VAR startOfPeriod = MIN( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( cdm_worker ),
        FILTER (
            ALL(cdm_worker),
               ( cdm_worker[cdm_worker (2).cdm_employment.cdm_employmentstartdate] <= endOfPeriod
                         && cdm_worker[cdm_worker (2).cdm_employment.cdm_employmentenddate] >= startOfPeriod && cdm_worker[cdm_worker (2).cdm_gendername] = "female")
        )
    )

Count of Active Employee - unspecified = 
VAR endOfPeriod = MAX ( 'Date'[Date] )

VAR startOfPeriod = MIN( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( cdm_worker ),
        FILTER (
            ALL(cdm_worker),
               ( cdm_worker[cdm_worker (2).cdm_employment.cdm_employmentstartdate] <= endOfPeriod
                         && cdm_worker[cdm_worker (2).cdm_employment.cdm_employmentenddate] >= startOfPeriod && cdm_worker[cdm_worker (2).cdm_gendername] = "unspecified")
        )
    )

Count of Active Employee - male = 
VAR endOfPeriod = MAX ( 'Date'[Date] )

VAR startOfPeriod = MIN( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( cdm_worker ),
        FILTER (
            ALL(cdm_worker),
               ( cdm_worker[cdm_worker (2).cdm_employment.cdm_employmentstartdate] <= endOfPeriod
                         && cdm_worker[cdm_worker (2).cdm_employment.cdm_employmentenddate] >= startOfPeriod && cdm_worker[cdm_worker (2).cdm_gendername] = "male")
        )
    )

I have a table below this bar chart which has the employee names. However, it only provides employees who have been hired within those date ranges. For example we hired one employee in July, so when I click July on the bar chart it only shows me that one employees name in the table below rather than all 106 employees that were active - with the employee who was currently hired.

Regards,

Henry

[tamerj1]

Hi @DynamicsHS 
In this case you need to do the oppsite. You need to remove the filter from the date table and then iterate ove the table to manually count the number of employees that are active in the selected 'Date'[Date] in the filter context. Something like:

=
CALCULATE (
    SUMX (
        Payroll,
        IF (
            Payroll[Start Date] <= MAX ( 'Date'[Date] )
                && Payroll[End Date] >= MAX ( 'Date'[Date] ),
            1
        )
    ),
    REMOVEFILTERS ( 'Date' )
)

[DynamicsHS]

Hi @tamerj1

I gave this ago and unfortunaently nothing came up? 

Thanks,

Henry

[tamerj1]

@DynamicsHS 

Instead of REMOVEFILTERS you may try CROSSFILTER to NONE