Forum Discussion
"While loop" in PowerBI - Please help!
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 🙂
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
4 Replies
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.
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?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
