Increment value as long as switch condition is met

[msantillan]

As the title implies I would like to have a counter which adds + 1 everytime a switch condition is met. 

I created a query but it doesn't seem to add into the variable x.

Query used: 

counter =

var x = 0

RETURN

SWITCH(

TRUE(),

'Scorecard 2'[Email CSAT] >= .85, x + 1,

'Scorecard 2'[Email AHT] <= 150, x + 1,

'Scorecard 2'[Quality] >= .9, x + 1,

0

)

Outcome:

Employee	Email CSAT	Email AHT	Quality	counter
1	95%	209	69%	1
2	94%	88	87%	1

Desired Outcome:

Employee	Email CSAT	Email AHT	Quality	counter
1	95%	209	69%	1
2	94%	88	87%	2

Anyone knows where I went wrong with my query, any help would be appreciated.

[v-chenwuz-msft · Accepted solution]

Hi @msantillan ,

The logical relationship is not correct. SWITCH() will judge in order, and if the first one is satisfied, it will output the result directly and will not continue to judge.

It should be a recursive relationship. Try the following formula.

counter =
VAR x = 0
VAR _1 =
    IF ( [Email CSAT] >= .85, x + 1 )
VAR _2 =
    IF ( [Email AHT] <= 150, _1 + 1, _1 )
RETURN
    IF ( [Quality] >= .9, _2 + 1, _2 )

result:

Best Regards

Community Support Team _ chenwu zhu

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

[sturlaws]

Hi,

You provide quite a small amount of information, so I will assume that you need a calculated column, and that [employee] is a unique integer for each employee. A calculated column could be written like this:

counter = 
VAR _currentEmployee =
    CALCULATE ( SELECTEDVALUE ( employees[Employee] ) )
RETURN
    COUNTROWS (
        FILTER (
            ALL ( employees ),
            employees[Employee] <= _currentEmployee
                && ( employees[Email CSAT] >= 0.85
                || employees[Email AHT] <= 150
                || employees[Quality] >= 0.9 )
        )
    )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.