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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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