check if employee is active on a certain date.

[tproost]

So i have been searching on this formula for a couple of days and can't figure it out

I am building a report to check if employees where present at certain events. However to calculate the totals for an event i need to know who was actually working at the date of that event.

I get this data from multiple tables

There is one table with info about the event containing a column with the actual date.

There is a second table with employees who are present at the event, linked with an event id to the actual event

Then i have a 3th table with employee information including in what team they are and their employment dates. Problem is that every time a change was made any parameter of their employment status there is a new entry with the same employee id but different dates (I have no control over this data source, this is an extract from the HR system)

 eg.

employee	StartDate	EndDate
Bob	5-aug-2020	10-aug-2020
Bob	11-aug-2020	30-aug-2020
Bob	31-aug-2020	5-May-2021
Geoff	1-aug-2020	13-aug-2020
Geoff	14-aug-2020	30-aug-2020
Geoff	31-aug-2020	10-Apr-2021
Geoff	11-Apr-2020	1-Dec-9999

The problem is with this Table,

My report needs to show for each conference how many people where present and how many people are active in the team. I have a measure for my total present team members but when i try to build the measure for the total active team members on the given date the formula trows me an error that there is a many to one ration in this table.

A single value for column 'StartDate' in table 'EmployeeActive' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

the problem is that each period needs to be validated, i can not simply assume that the employees first employment date and last employment date in that period are the only dates, there could be a period in between where he/she was not active.

[Ashish_Mathur]

Hi,

That table is not proper.  You should transform the table to show one data per cell for each employee.  Delete the 1 Dec 9999 date.  This M code does that

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRMjDVdSxN1zUyALINDWDsWB2YvKEhQt4Yi7wxkjzQLN/ESl0jQ7C8e2p+WhpIFEmFoTGyCTAVhia47ICpQLYF5MqCInRbQO4EiYJUKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [employee = _t, StartDate = _t, EndDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"employee", type text}, {"StartDate", type date}, {"EndDate", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [EndDate]=null then DateTime.Date(DateTime.LocalNow()) else [EndDate]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"EndDate"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Date", each {Number.From([StartDate])..Number.From([Custom])}),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom1", "Date"),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Date",{"StartDate", "Custom"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Date", type date}})
in
    #"Changed Type1"

The end result of the M code will be:

[v-easonf-msft]

Hi,  @tproost 

Not very clear.

Please share a sample pbix file for further research.

You can also refer to these related documents to check the error message.

https://www.sqlbi.com/articles/dax-error-messages-in-power-bi/ 

Error-This-can-happen-when-a-measure-formula-refers-to-a-column 

a-single-calue-for-column-cannot-be-determined 

Best Regards,
Community Support Team _ Eason

[amitchandak]

@tproost , refer if my HR blog - the current employee measure can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...