cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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:

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

1 ACCEPTED SOLUTION
Super User

@Ash88 , try like

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

3 REPLIES 3
Super User

Try this:

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

@Ash88 , try like

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

Frequent Visitor

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.