calculate count when no value exsists

[dw700d]

I have a table that shows a list of days all employees came into the office. If an employee enters the office on a specific date the “Badged in” Column has a Yes. My table does not show me data  when an employee does not come into the office. See the sample data below.

Date	Full Name	Badged In	WorkCity
8-Dec-22	Chad	Yes	San Antonio
7-Dec-22	Chad	Yes	San Antonio
30-Nov-22	Chad	Yes	San Antonio
29-Nov-22	Daniel	Yes	ATLANTA
22-Nov-22	Daniel	Yes	ATLANTA
17-Nov-22	Daniel	Yes	ATLANTA
15-Nov-22	Diana	Yes	San Antonio
11-Nov-22	Diana	Yes	San Antonio
9-Nov-22	Diana	Yes	San Antonio
1-Nov-22	G	Yes	ATLANTA
24-Feb-23	G	Yes	ATLANTA
21-Feb-23	G	Yes	ATLANTA

I wanted to create a measue that counts the amount of times an employee came into the office and if an employee has never come into the office the value would be 0. I connected the above table to an employee table with all employeees (this includes employees that have come to the office and those that have not). The measure is below

Entrance Count = CALCULATE(COUNT('Export'[Badged In]))+0

 It works fine with 1 column. as shown below

Full name	EntranceCount
Chad	22
Daniel	27
Diana	21
G	0
Derick	30
Dave	10
Cheryl	21
Joseph	24
Val	0

When I add two columns as shown below it does not work correctly. It creates multiple rows. For example chads WorkCity is located in Sanantonio and his entrance count correctly shows 22.  but becasue of the way my measure is constrcued it creates two addittional rows and adds a 0 since he has never badged into an Atlanta Workcity or Dallas workcity. How can I adjust my measure to be able to add multiple columns withouht creating addittional rows.

Full Name	WorkCity	EntranceCount
Chad	ATLANTA	0
Chad	DALLAS	0
Chad	SAN ANTONIO	22
Daniel	ATLANTA	27
Daniel	DALLAS	0
Daniel	SAN ANTONIO	0
Diana	ATLANTA	0
Diana	DALLAS	0
Diana	SAN ANTONIO	21
G	ATLANTA	0
G	DALLAS	0
G	SAN ANTONIO	0
Derick	ATLANTA	30
Derick	DALLAS	0
Derick	SAN ANTONIO	0

Desired output

Full Name	WorkCity	Entrance Count
Chad	SAN ANTONIO	22
Daniel	ATLANTA	27
Diana	SAN ANTONIO	21
G	ATLANTA	0
Derick	ATLANTA	30

[Anonymous · Accepted solution]

Hi @dw700d 

You can use if() to judge.

e.g 

IF(SELECTEDVALUE(City[WorkCity]) in VALUES('Export'[WorkCity]),CALCULATE(COUNT('Export'[Badged In])+0))

Best Regards!

Yolo Zhu

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