Forum Discussion
Display chart based on slicer value
I have created two charts to display the NPS scores of clients, One is a line chart showing the NPS score over multiple years and the other is a tachometer chart to display the NPS score, Now, if I ...
So this is the sample data I have
| Session date | Client | Programme | Event | NPS |
| 12/3/2022 | Client 1 | Prog C1 1 | Skills for tomorrow | 7 |
| 12/3/2022 | Client 1 | Prog C1 1 | Skills for tomorrow | 4 |
| 14/3/2022 | Client 1 | Prog C1 3 | CV writing | 6 |
| 14/3/2022 | Client 1 | Prog C1 3 | CV writing | 9 |
| 16/3/2022 | Client 2 | Prog C2 1 | Digital Marketing | |
| 16/3/2022 | Client 2 | Prog C2 1 | Digital Marketing | |
| 16/3/2022 | Client 2 | Prog C2 1 | Audience developemnt | 7 |
| 16/3/2022 | Client 2 | Prog C2 1 | Audience developemnt | 10 |
| 12/3/2023 | Client 2 | Prog C2 1 | Marketing techniques | 9 |
| 12/3/2023 | Client 2 | Prog C2 1 | Marketing techniques | 3 |
| 12/3/2023 | Client 2 | Prog C2 1 | Marketing techniques | 2 |
| 12/3/2023 | Client 2 | Prog C2 1 | Marketing techniques | 0 |
| 21/3/2023 | Client 2 | Prog C2 2 | Marketing techniques |
Each row is a feedback response from our learners and NPS is basically the NPS score. What I want is that if data filtered in the slicers (Client, program, event) and the year is one single year I show Tachometer like this
But if multiple years are selected for say one client or multiple clients it should show a line chart like this
and if no data is available it should show the message 'No data found'
Hope it helps? v-zhangti
These are the measures I am using so far
for NPS:
NPS Score second =
//be sure you have a question in your survey asking "How likely would you be to recommend us?" with a scale of 0-10
VAR _tbl= FILTER('Merged',NOT(ISBLANK('Merged'[How likely are you to recommend the session?])))
VAR detractors = COUNTROWS(FILTER(_tbl,'Merged'[How likely are you to recommend the session?]<=6))
//don't forget to make your NPS question column into a whole number data type
VAR passives =COUNTROWS(FILTER(_tbl,'Merged'[How likely are you to recommend the session?]=7||'Merged'[How likely are you to recommend the session?]=8))
VAR promoters =COUNTROWS(FILTER(_tbl,'Merged'[How likely are you to recommend the session?]>=9))
VAR total = COUNTROWS(_tbl)
VAR score = (promoters/total*100)-(detractors/total*100)
RETURN score
Next, I am using a simple measure and some conditional formatting to show the no data message.
I am using this measuere to show and hide a visual
Distinct Years =
CALCULATE(
DISTINCTCOUNT(Merged[Session Date].[Year]),
FILTER(Merged, NOT(ISBLANK(Merged[NPS])))
)
hide = if(CALCULATE(SUMX(VALUES(Merged[Client]),[Distinct Years]),ALLSELECTED(Merged)) > 1,1,0)