Forum Discussion
Countif replicate in PowerBI
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.
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])))
2 Replies
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])))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 _ CaitlynIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
