Countif replicate in PowerBI

[phildavies2022]

Hi all,

I've tried all day but give up 😌.  I have 2 tables that have a relationship linked by the Location Code

Table 1: 'ActiveEmployees',[Employee ID]

Name, Employee ID, Start Date, End Date, Location Code, Status

Table 2: 'PreviousEmployees',[Employee ID]

Name, Employee ID, Start Date, End Date, Location Code, Status

I want to count how many times an Employee ID appears on the PreviousEmployees table.  This will help to see how many times someone has worked for us in the past.  The employee would always have the same employee ID number, even if they have worked for us in the past, so the unique identifier is the employee ID number.

I have tried to do DISTINCTCOUNT, in Excel I would have a countif and it works easily, but I cannot do this in PowerBI.  Any help is appreciated.  Thanks all.

[Anonymous]

Hi @phildavies2022 ,

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thanks in advance for your kind cooperation!

Hope it helps,

Community Support Team _ Caitlyn

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

[smpa01 · Accepted solution]

@phildavies2022  drop    t1[Employee ID] as axis in a viz and drop the following measure

measure = CALCULATE(COUNT(t2[emp code]),TREATAS({MAX(t1[emp code])},t2[emp code]),REMOVEFILTERS(t2))

one more

measure2 = CALCULATE(COUNT(t2[emp code]), FILTER(ALL(t2), t2[emp code] in VALUES(t1[emp code])))