Handle Inactive Relationship for Count Measures

[Ramees_123]

I have an Employee table with Joining Date and Emp ID.

I also have a DimDate table with date values.

Note: The Joining Date in Employee Table and the Date field in DimDate table have only InActive Relationship. 

I have a year slicer in my report connected to DimDate table - Date field.

Now I need the count of employess who joined on that selected year mentioned in slicer. 

How to do it. Please help.

[v-yiruan-msft · Accepted solution]

Hi @Ramees_123 ,

You can create a measure as below:

Count of employees =
CALCULATE (
    DISTINCTCOUNT ( 'Employee'[Employee ID] ),
    USERELATIONSHIP ( 'Employee'[Date], 'DimDate'[Date] )
)

In addition, you can refer the content in the following links to achieve it.

Power BI – Using inactive relationships in a measure

UseRelationship or Role-Playing Dimension; Dealing with Inactive Relationships in Power BI

Best Regards

[webportal]

Build a matrix visual.

Rows: Year

Values: distinct count of employee ID

This is assuming the date table is related to the employees table by joining date.