Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
msantillan
Helper II
Helper II

Increment value as long as switch condition is met

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:
 
EmployeeEmail CSATEmail AHTQualitycounter
195%20969%1
294%8887%1

 

Desired Outcome:

EmployeeEmail CSATEmail AHTQualitycounter
195%20969%1
294%8887%2
 
Anyone knows where I went wrong with my query, any help would be appreciated.
1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

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:

vchenwuzmsft_0-1654743704018.png

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.

 

View solution in original post

2 REPLIES 2
v-chenwuz-msft
Community Support
Community Support

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:

vchenwuzmsft_0-1654743704018.png

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
Resident Rockstar
Resident Rockstar

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.