Forum Discussion

laurent_rio's avatar
laurent_rio
Helper I
5 years ago
Solved

Filter in Union Function

Hi i have 3 tables    1 is employee list  Employee ID 1 2 3 4 5 6   2 is Employee Status  Employee ID Employee Status 1 Active 3 Active 4 Terminated 5...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi laurent_rio ,

     

    Please use the following measure.

     

    My Measure = CALCULATE(DISTINCTCOUNT('Employee ID'[Employee ID]),FILTER('Employee ID',LOOKUPVALUE('Employee Status'[Employee Status],'Employee Status'[Employee ID],'Employee ID'[Employee ID]) = "active"))

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

AllisonKennedy's avatar
AllisonKennedy
Community Champion
5 years ago

laurent_rio  You don't need a UNION here - union will stack both tables on top of each other. 

 

Does the status table have each employee multiple times? Is there a date field to indicate the most recent entry and therefore current status? 

 

Are these two tables related in the data model?