Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Modify Existing Created Measure to include DistinctCount

Hi,

 

I have a created measure that is no longer doing what I need it to do because the data source has slightly changed.  Here is the current created measure:

 

Employer Group ER % = Divide(Calculate(Sum('Data Files'[ER_VISITS]))*1000,(DISTINCTCOUNT('Data Files'[MBR_ID])))

 

Essentially, it is finding the SUM of [ER_Visits] and multiplying that value by 1000.  It is then dividing this value over the DistinctCount of [MBR_ID].

 

The issue is that my data source has slightly changed.  Now, the field [MBR_ID] can populate numerous times in the data source so my old measure is duplicating the [ER_Visits].  I need to somehow have the measure find the Distinct [MBR_ID]s and then find the Sum of [ER_Visits].  I need this value to become my numerator. I just dn't know how to incorporate this in to my measure.

 

The denominator portion of the formula is fine.  I just need to modify the numerator so it is not duplicating the [ER_Visits] for potential {MBR_ID]s that are in the data source more than once. 

 

 

Any ideas?

Thank you,

Andrew

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , Try like

 

Employer Group ER % = Divide(sumx(values('Data Files'[MBR_ID]),calculate(max('Data Files'[ER_VISITS])))*1000,(DISTINCTCOUNT('Data Files'[MBR_ID])))

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@Anonymous , Try like

 

Employer Group ER % = Divide(sumx(values('Data Files'[MBR_ID]),calculate(max('Data Files'[ER_VISITS])))*1000,(DISTINCTCOUNT('Data Files'[MBR_ID])))

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.