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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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