"While loop" in PowerBI - Please help!

[jakeryan56]

Hey guys, I am very desperate for a solution to this problem!

Firstly you'll need to be familire with NPS scores - here is some background.

I need to calculate for a given NPS score target, how many Promoters would be required to achieve that.

The only solution I could come up with at the moment is creating multiple variables and then adding one Promoter (and one extra survey count) to each variable and then running an if statement to see which variable returns true.

This is extremely impractical because I need this "loop" to run at least 500 times.  

I've been trying to adapt this post on an alternative "while-loop" proxy but just can't make it work.  Please help 🙂

[Greg_Deckler]

@jakeryan56 Would need sample data. In general, to use a "while loop" in DAX you need to use GENERATESERIES or a table to iterate over. You need to make a calculation at each row and then you filter the table for the first value that meets the goal criteria.

[jakeryan56]

@Greg_Deckler  You were the exact person I was hoping would reply!

I tried followed your guides but for the life of me couldn't make it work.  
I have put together some sample data here.  Would you be able to take a quick look?

[Greg_Deckler · Accepted solution]

@jakeryan56 Perhaps below. Basically, use GENERATESERIES to create a table from 1 to whatever in increments of 1. This is the basis for the WHILE loop. The ADDCOLUMNS emulates the loop. At each [Value] you are doing your NPS calculation again but adding the number of promoters specified by the Value column. Once you are done calculating all possible values for your loop, your MINX essentially emulates the "break" condition for your while loop. You are finding the minimum [Value] where your NPS score is equal to or greater than your goal. Once you have that, you just return the value.

Promoters Required 2 = 
    VAR __CurrentNPS = [NPS_Score]
    VAR __GoalNPS = [Goal_NPS_Score]
    VAR __SurveyCount = [SurveyCount]
    VAR __PromoterSum = [Promoter_SUM]
    VAR __DetractorSum = [Detractor_SUM]
    VAR __Promoters = 
        ADDCOLUMNS(
            GENERATESERIES(1,10000,1),
            "__NPS", ((__PromoterSum+[Value])/(__SurveyCount+[Value]) - (__DetractorSum/(__SurveyCount+[Value])))*100
        )
    VAR __Required = MINX(FILTER(__Promoters,[__NPS]>=__GoalNPS),[Value])
RETURN
    __Required

[jakeryan56]

@Greg_Deckler  You are the best that worked perfectly!! 
THANK YOU SO MUCH.  You have no idea the hours I have wasted on this and turns out I was very close.

Virtaul beer for you 🍺