cancel
Showing results for
Did you mean:
Frequent Visitor

## 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 2
Super User

## Re: DAX Measure: Distinct Sum based on two other columns

Can you simply not take the MIN or MAX or Average of Entitlement Column?

Try my new Power BI game Cross the River
Super Contributor

## Re: DAX Measure: Distinct Sum based on two other columns

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.

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,415)