How to get data according to selected value of slicer

[Anonymous]

Hi,

I have all employee data including active, inactive employee with their all information in GetAllEmployeeDetails table.
I have year,month,company,branch slicer which has relation with GetAllEmployeeDetails.

Now when user selects year. I want to show all employee list which was working in that SelectedYear.
Logic can be - All employee list from GetAllEmployeeDetails whose cur_status = "Active" and
All employee list from GetAllEmployeeDetails whose leaving_dt = SelectedYear

SelectedYear is already created measure in which we are getting selected slicer year

SelectedYear = SELECTEDVALUE ( DateDim[Date].[Year] )<br /><br />TotalEmpOfSelectedDate = 
UNION (
    FILTER (
        GetActiveEmployeeDetails,
        GetActiveEmployeeDetails[cur_status] = "Active"
    ),
    FILTER (
        GetAllEmployeeDetails,
        YEAR ( GetAllEmployeeDetails[leaving_dt] ) = [SelectedYear] 
    )
)

For above code I am getting error - Each table argument of 'UNION' must have the same number of columns.
How it would be possible to not having same number of columns, I am applying union on same Table.

I tried so many solution from youtube, google but I am not getting what i wanted, sometimes i am getting error like
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value with different queries.

Please help me out.

Thanks in Advance.

[DataNinja777]

Hi @Anonymous ,

Regarding the headcount analysis, with your current data, "Active" or not is dependent on a particular point in time.  For more flexibility with respect to time, it is recommended that you apply a headcount analysis which frees you from the constraints of the time dimension.  The way to do a flexible headcount analysis, which I believe should be applied by any HR person as a standard method, is to use an employee table of start date and end date with a disconnected calendar table.  If you use this methodology, your "Active" or not information can be obtained for any point in time in your calendar table without any constraints of being tied to any particualr point in time.  

Best regards,

[Anonymous]

Hi @DataNinja777 ,
Thank for reply.
Can you please ellaborate as I am new to Powerbi.

Thanks