Count and Sum of one unique value across multiple columns

[Anonymous]

Table

Hi guys,

To provide some context to the table above, we have an office and one person can come into the office either in the morning or the afternoon. The table above indicates who will be going into the office in the morning and who will be going in the afternoon. 

I would like to have a table visual on PowerBI that sums up how many times an individual will be going into the office like so: 

Table2

Would appreciate your help.

Thanks in advance.

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

You can append two columns in the power query and remove the duplicate values and use the new table formed as a list. Then count the corresponding values in both columns.

If the problem is still not resolved, please point it out. Looking forward to your reply.

Best Regards,
Henry

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

[amitchandak]

@Anonymous , You need create a table with all names

Name = distinct(union(distinct(Table[Morning]), distinct(Table[afternoon]) ))

then join it with both name, assume Morning join is active

Count(Table[Name]) + calculate(count(Table[Name]) , userealtionship( Table[afternoon], Name[Morning]) )

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

[Anonymous]

Thanks for your reply @amitchandak 

So just to confirm with you: lets say the table name of the breakdown of people going in the morning/afternoon is named "Office_Roster". I need to create a second table with the names, I named this second table 'Office_Members'.

Then I create the following first measure: 

Name = distinct(union(distinct(Office_Roster[Morning]), distinct(Office_Roster[afternoon]) ))

and afterwards I didnt quite understand your expression "Count(Table[Name]) + calculate(count(Table[Name]) , userealtionship( Table[afternoon], Name[Morning]) )"