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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Ash88
Frequent Visitor

DISTINCTCOUNT using a measure that calculates over multiple rows

Hi all,

 

Fairly new to DAX so apologies if I've missed something obvious. I am trying to create a measure that returns a distinct count of a managers employees who achieved or exceeded the NPS target (say +60). NPS Score is calculated using a simple measure:

 

 

 

NPS Score:=DIVIDE(SUM([Promoter])-SUM([Detractor]),SUM([NPS Survey Count]))*100

 

 

The raw data looks like this:

 

DateEmployeeManager level 1Manager level 2NPSPromoterDetractorNPS Survey Count
1/1/23John SmithAndrew BrownJulie Jackson10101
16/1/23John SmithAndrew BrownJulie Jackson4011

 

I tried the below formula but it is counting each row where NPS was over target (any surveys scored 8+ on NPS). Where I would want it to count John Smith just once if his NPS score was 60 or over for the month (or whichever date context we're using).

 

 

 

UniqueAgentsAtTarget:=CALCULATE(DISTINCTCOUNT(NPS_Data[Employee]),filter(NPS_Data,[NPS Score]>=60))

 

 

 

Thanks for reading, if you need any more info let me know!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Ash88 , try like

UniqueAgentsAtTarget:=Countrows(Filter(values(NPS_Data[Employee]),[NPS Score]>=60))

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

3 REPLIES 3
AlexisOlson
Super User
Super User

Try this:

UniqueAgentsAtTarget =
VAR Scores =
    ADDCOLUMNS ( DISTINCT ( NPS_Data[Employee] ), "@Score", [NPS Score] )
RETURN
    COUNTROWS ( FILTER ( Scores, [@Score] >= 60 ) )
amitchandak
Super User
Super User

@Ash88 , try like

UniqueAgentsAtTarget:=Countrows(Filter(values(NPS_Data[Employee]),[NPS Score]>=60))

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

Thanks for this! If I needed to change the target to a variable monthly target in future, do you know how I could implement that into this formula? IE target in a future month may increase from 60 to 65.

Helpful resources

Announcements
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.