DAX for NPS

[Anonymous]

Hi,

I need to calculate NPS Score but only need to look at latest reponses (By Date) and respective topics.

I am starting with Promotor and Detractors for now .Below is the data and expected output. 

If I get dyamic ranking for it , should be able to filter based on Rank for Promotors and detractors.

Started with Rank but having a hardtime in incorporating topicwise rating...

Any help is appreciated.

Ranks = RANKX(FILTER(Table,Table[Person_EmpID]=EARLIER(Table[Person_EmpID])),Table[Date].[Date],,ASC,Dense)

Here is the raw data

Topic	Person Name	Rating	Date
A	Adam	10	11/20/2019
A	Adam	9	11/28/2019
A	Adam	4	11/29/2019
B	Adam	3	11/30/2019
A	Ben	10	10/30/2019
A	Ben	9	10/20/2019
A	Ben	3	11/5/2019
B	Ben	1	11/3/2019
B	Ben	2	11/4/2019

Below is the expected output

Topic	Person Name	Score	Date	Promotor By Topic	Detractor By Topic	Promotor Overall	Detractor Overall
A	Adam	10	11/20/2019
A	Adam	9	11/28/2019	1		1
A	Adam	4	11/29/2019		1
B	Adam	3	11/30/2019		1		1
A	Ben	10	10/30/2019	1		1
A	Ben	9	10/20/2019
A	Ben	3	11/5/2019		1		1
B	Ben	1	11/3/2019
B	Ben	2	11/4/2019		1

Thanks

[v-jayw-msft]

Hi @Anonymous ,

I managed to figure out the Detractors but failed about Promotors. Actually, I couldn't understand what the Promotors is according to your expected output.

Please check following measures and see if the result achieve your expectation:

Promotor By Topic = IF(MAX('Table'[Date]) = CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[Person Name],'Table'[Topic])),1,"")
Detractor By Topic = IF(MAX('Table'[Date]) = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Person Name],'Table'[Topic])),1,"")
Promotor Overall = IF(MAX('Table'[Date]) = CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[Person Name])),1,"")
Promotor By Topic = IF(MAX('Table'[Date]) = CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[Person Name],'Table'[Topic])),1,"")

Result would be shown as below:

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for that.

Promotors are score between 9 and 10.

The DAX you provided works after chnaging MIN to MAX  but If anyone changes the date slicer it doesn't chnage with it.

If its showing a count of 1 for November , It doesn't show count 1 for Oct dynamically even though slicer has changed until Oct.

IF(MAX('Table'[Date]) = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Person Name],'Table'[Topic])),1,"")

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

In that case you might need a Calendar table and SELECTEDVALUE() function.

Please refer to below measures and see if the result achieve your expectation:

Promotor By Topic = 
IF (
    ISFILTERED ( 'Table 2'[YearMonth] ),
    IF (
        MAX ( 'Table'[Date] )
            = CALCULATE (
                MAX ( 'Table'[Date] ),
                FILTER (
                    ALLEXCEPT ( 'Table', 'Table'[Person Name], 'Table'[Topic] ),
                    'Table'[Rating] >= 9
                        && FORMAT ( 'Table'[Date], "YYYYMM" ) <= SELECTEDVALUE ( 'Table 2'[YearMonth] )
                )
            ),
        1,
        ""
    ),
    IF (
        MAX ( 'Table'[Date] )
            = CALCULATE (
                MAX ( 'Table'[Date] ),
                FILTER (
                    ALLEXCEPT ( 'Table', 'Table'[Person Name], 'Table'[Topic] ),
                    'Table'[Rating] >= 9
                )
            ),
        1,
        ""
    )
)
Detractor By Topic = 
IF (
    ISFILTERED ( 'Table 2'[YearMonth] ),
    IF (
        MAX ( 'Table'[Date] )
            = CALCULATE (
                MAX ( 'Table'[Date] ),
                FILTER (
                    ALLEXCEPT ( 'Table', 'Table'[Person Name], 'Table'[Topic] ),
                    FORMAT ( 'Table'[Date], "YYYYMM" ) <= SELECTEDVALUE ( 'Table 2'[YearMonth] )
                )
            ),
        1,
        ""
    ),
    IF (
        MAX ( 'Table'[Date] )
            = CALCULATE (
                MAX ( 'Table'[Date] ),
                ALLEXCEPT ( 'Table', 'Table'[Person Name], 'Table'[Topic] )
            ),
        1,
        ""
    )
)
Promotor Overall = 
IF (
    ISFILTERED ( 'Table 2'[YearMonth] ),
    IF (
        MAX ( 'Table'[Date] )
            = CALCULATE (
                MAX ( 'Table'[Date] ),
                FILTER (
                    ALLEXCEPT ( 'Table', 'Table'[Person Name] ),
                    'Table'[Rating] >= 9
                        && FORMAT ( 'Table'[Date], "YYYYMM" ) <= SELECTEDVALUE ( 'Table 2'[YearMonth] )
                )
            ),
        1,
        ""
    ),
    IF (
        MAX ( 'Table'[Date] )
            = CALCULATE (
                MAX ( 'Table'[Date] ),
                FILTER ( ALLEXCEPT ( 'Table', 'Table'[Person Name] ), 'Table'[Rating] >= 9 )
            ),
        1,
        ""
    )
)
Detractor Overall = 
IF (
    ISFILTERED ( 'Table 2'[YearMonth] ),
    IF (
        MAX ( 'Table'[Date] )
            = CALCULATE (
                MAX ( 'Table'[Date] ),
                FILTER (
                    ALLEXCEPT ( 'Table', 'Table'[Person Name] ),
                    FORMAT ( 'Table'[Date], "YYYYMM" ) <= SELECTEDVALUE ( 'Table 2'[YearMonth] )
                )
            ),
        1,
        ""
    ),
    IF (
        MAX ( 'Table'[Date] )
            = CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Person Name] ) ),
        1,
        ""
    )
)

Result would be shown as below:

BTW, Pbix as attached, hopefully works you.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jayw-msft]

Hi @Anonymous ,

Has the question been solved, did my answer help you?

Could you please accept my post as the solution if it helps?

Please feel free to ask if you have any questions.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.