Forum Discussion

laurent_rio's avatar
laurent_rio
Icon for Helper I rankHelper 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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

laurent_rio , You need to a common table

 

Employee ID  = DISTINCT (
UNION (
all ( Table1[EmployeeID] ),
all ( Table2[EmployeeID] ),
)
)

 

You can now join it with both table and get status from table 2 in visual

 

Or add a new column in Employee ID  table

 

maxx(filter(Table2, Table[Employee ID] = 'Employee ID'[Employee ID]), Table2[Employee Status])