Forum Discussion
Advocate II"While loop" in PowerBI - 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
Community Championjakeryan56 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.
Advocate IIGreg_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
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 🍺