Formatting dataset for Venn diagram

[Anonymous]

Hi,

I am trying to format a dataset for venn diagram in power bi. I want to find out how many employees have done Morning, afternoon and evening shifts and combination of all three. For example, an employee 1 has done both Afternoon and Evening shift.

Current DataSet :

Employee ID	Morning	Afternoon	Evening
1	0	0	1
1	0	1	0
2	1	0	0
3	0	1	0
4	1	0	0
4	0	1	0
4	0	0	1

 Expected result:

Employee ID	Morning	Afternoon	Evening
1	0	1	1
2	1	0	0
3	0	1	0
4	1	1	1

DistinctCount of Employee ID	Morning	Afternoon	Evening
0	0	0	0
1	1	0	0
1	0	1	0
0	0	0	1
0	1	1	0
1	0	1	1
0	1	0	1
1	1	1	1

Any views on this topic is appreciated !

Thanks

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may create the tables with SUMMARIZE Function.Attached sample file for your reference.

Summarize =
SUMMARIZE (
    'DataSet',
    'DataSet'[Employee ID],
    "Morning", MAX ( 'DataSet'[Morning] ),
    "Afternoon", MAX ( 'DataSet'[Afternoon] ),
    "Evening", MAX ( 'DataSet'[Evening] )
)

Regards,