Forum Discussion
DAX Measure: Distinct Sum based on two other columns
Snippet of data is shown below - As you can see, for each Employee and Period, the Entitlement is repeated on every row.
I'm essentially after a measure that produces the Entitlement for each Employee / Period.
Each row represents an actual holiday taken, so it would contain other columns like 'hours taken', 'holiday date' - so i can't reformat the data to remove the duplication, I need a measure that calculates the right value.
| Emp_ID | Entitlement | PeriodStart |
| 12345678 | 187.5 | 01/11/2007 |
| 12345678 | 187.5 | 01/11/2007 |
| 98765432 | 200 | 01/11/2007 |
| 98765432 | 200 | 01/11/2007 |
| 12345678 | 187.5 | 01/11/2008 |
| 12345678 | 187.5 | 01/11/2008 |
| 12345678 | 187.5 | 01/11/2008 |
| 98765432 | 205 | 01/11/2008 |
| 98765432 | 205 | 01/11/2008 |
| 12345678 | 255 | 01/11/2009 |
| 12345678 | 255 | 01/11/2009 |
| 12345678 | 255 | 01/11/2009 |
| 12345678 | 255 | 01/11/2009 |
| 98765432 | 240 | 01/11/2009 |
| 98765432 | 240 | 01/11/2009 |
| 98765432 | 240 | 01/11/2009 |
| 98765432 | 240 | 01/11/2009 |
For example, if in Power BI I threw Emp_Id, PeriodStart and my new measure into a table, it would look like this:
| Emp_ID | Entitlement | PeriodStart |
| 12345678 | 187.5 | 01/11/2007 |
| 98765432 | 200 | 01/11/2007 |
| 12345678 | 187.5 | 01/11/2008 |
| 98765432 | 205 | 01/11/2008 |
| 12345678 | 255 | 01/11/2009 |
| 98765432 | 240 | 01/11/2009 |
2 Replies
Hi Celador,
To achieve your requirement, I think you can try to create a new calculated table with SUMMARIZE() function:
New Table = SUMMARIZE ( Duplicates, Duplicates[Emp_ID], Duplicates[Entitlement], Duplicates[PeriodStart] )Thanks,
Xi Jin.
