DISTINCTCOUNT using a measure that calculates over multiple rows

[Ash88]

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:

Date	Employee	Manager level 1	Manager level 2	NPS	Promoter	Detractor	NPS Survey Count
1/1/23	John Smith	Andrew Brown	Julie Jackson	10	1	0	1
16/1/23	John Smith	Andrew Brown	Julie Jackson	4	0	1	1

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!

[AlexisOlson]

Try this:

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

[amitchandak · Accepted solution]

@Ash88 , try like

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

[Ash88]

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.