DAX

[prashantg364]

I have a data table below where we can see employees worked under which scope on a particular date.

I want to create an output table where I would like to see distinct employees worked under which scope at the week level.

Employee	scope	Date	WK
alpha	SST	01-Jan-23	1
alpha	CVT	02-Jan-23	1
alpha	SST	03-Jan-23	1
beta	CLT	04-Jan-23	1
beta	CLT	05-Jan-23	1
beta	CLT	06-Jan-23	1
gama	CVT	07-Jan-23	1
gama	CVT	08-Jan-23	2
gama	CVT	09-Jan-23	2
alpha	SST	10-Jan-23	2
alpha	SST	11-Jan-23	2
alpha	SST	12-Jan-23	2
beta	SST	13-Jan-23	2
beta	SST	14-Jan-23	2
beta	SST	15-Jan-23	3
gama	CLT	16-Jan-23	3
gama	CLT	17-Jan-23	3
gama	CLT	18-Jan-23	3
alpha	SST	19-Jan-23	3
alpha	CVT	20-Jan-23	3
alpha	SST	21-Jan-23	3
beta	SST	22-Jan-23	4
beta	CVT	23-Jan-23	4
beta	CVT	24-Jan-23	4
gama	SST	25-Jan-23	4
gama	CVT	26-Jan-23	4
gama	SST	27-Jan-23	4

required Output table

Scope	SST	CVT	CLT	SST+CVT
Wk1	0	1	1	1
Wk2	2	1	0	0
Wk3	1	0	1	1
Wk4	0	0	0	2

[danextian]

hI @prashantg364 ,

Can you please double check? Your sample data does not match  with your expected result.  For example, there are supposed to be 1, 2, 2 and 2 for each week under SST but yours shows differently.  Also, it is unclear whether the output is in a visual or how the data should be loaded into the model.

Anyways, the attached pbix  might be what you're looking for.

[prashantg364]

The output is not correct.

Let me re-phrase my question.
I have data with employee's name & the work done on a particular date. Now I want to summarize my table to know how many unique employees worked in one scope & how many unique employees worked in multiple scopes.

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file if it suits your requirement.

All measures are in the attached pbix file.