Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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
Solved! Go to 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.
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.
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,"")
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.
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.