How to count number of yes/no from two separate columns in a table for pie chart?

[matthewtjy]

Workplace	Does workplace have a water cooler?	Does the workplace have a microwave?
A	Yes	No
B
C	Yes	Yes
D	No	Yes
E
F	No	No
G	Yes	No
H	No	No
I	Yes	Yes
J

So what I would like to achieve is have a pie chart visualisation that shows out of these 10 workplaces, how many have a "YES" in either having a water cooler or microwave.

Those workplaces without values were closed down.

So the pie chart should show 50% Yes (from A, C, D, G, I because they have "YES" in either columns) and 50% No (from B, E, F, H, J because they have "NO" in both columns/no values at all).

[v-eqin-msft · Accepted solution]

Hi @matthewtjy ,

If there are many other columns like [Does workplace have a water cooler?]  or [Does the workplace have a microwave?] ,please kindly refer to @Tahreem24 's method by pivoting the original table.

Or if there are just the two columns, please add a column with "Yes" or "No" flag:

Yes or No = 
var _combine= COMBINEVALUES("," , [Does workplace have a water cooler?] ,[Does the workplace have a microwave?])
return IF(CONTAINSSTRING(_combine,"Yes"),"Yes","No")

Then create Pie visual:

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

[Tahreem24]

@matthewtjy try this:

Step1 : Go to power query editor and select Microwave and watercooler column then click on unpivot column under Transform tab.

Step 2: Close and Apply

Step 3: Create a measure like this:

Measure =  CALCULATE(count(WorkspaceTable[Workplace]),WorkspaceTable[Value]="Yes")

Step 4: Then take a pie chart and take this measure under values field and Attribute under Details field.

[amitchandak]

@matthewtjy , Create a table with Two values yes and no. Then join it with both columns. One join will be inactive, that you can activate using userelationship  Then you can have measure

Count(Table[Workplace]) +calculate(Count(Table[Workplace]) ,USERELATIONSHIP(dim[Yes/No],Table[Does the workplace have a microwave?]) )

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

[matthewtjy]

Hi I'm sorry but how do i go about to create a table with Two values yes and no. Then join it with both columns?