Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to Solution.
@Ash88 , try like
UniqueAgentsAtTarget:=Countrows(Filter(values(NPS_Data[Employee]),[NPS Score]>=60))
Try this:
UniqueAgentsAtTarget =
VAR Scores =
ADDCOLUMNS ( DISTINCT ( NPS_Data[Employee] ), "@Score", [NPS Score] )
RETURN
COUNTROWS ( FILTER ( Scores, [@Score] >= 60 ) )
@Ash88 , try like
UniqueAgentsAtTarget:=Countrows(Filter(values(NPS_Data[Employee]),[NPS Score]>=60))
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
87 | |
84 | |
68 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |