Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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.
Hi @Celador
Can you simply not take the MIN or MAX or Average of Entitlement Column?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |