cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Celador Frequent Visitor
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_IDEntitlementPeriodStart
12345678187.501/11/2007
12345678187.501/11/2007
9876543220001/11/2007
9876543220001/11/2007
12345678187.501/11/2008
12345678187.501/11/2008
12345678187.501/11/2008
9876543220501/11/2008
9876543220501/11/2008
1234567825501/11/2009
1234567825501/11/2009
1234567825501/11/2009
1234567825501/11/2009
9876543224001/11/2009
9876543224001/11/2009
9876543224001/11/2009
9876543224001/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_IDEntitlementPeriodStart
12345678187.501/11/2007
9876543220001/11/2007
12345678187.501/11/2008
9876543220501/11/2008
1234567825501/11/2009
9876543224001/11/2009
2 REPLIES 2
Super User
Super User

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

Hi @Celador

 

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

Try my new Power BI game Cross the River
v-xjiin-msft Super Contributor
Super Contributor

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

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]
)

 

11.PNG22.PNG

 

Thanks,
Xi Jin.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

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