Forum Discussion
DAX
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 |
3 Replies
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.
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.
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.
