Forum Discussion

DynamicsHS's avatar
DynamicsHS
Icon for Helper II rankHelper II
4 years ago

Can you use a USERELATIONSHIP for two dates?

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

Need Help

5 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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' )
)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      DynamicsHS
      Icon for Helper II rankHelper II
      4 years ago

      Hi Anonymous & 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