For loop in power bi

[Anonymous]

Hello!

I created a measure that calculate a score based on several variables. When I filter the table by the current week, I would need to build 2 columns:

1. One column including "yes" or "no" if this score was below 50 in at least one of the 5 previous weeks.

2. One column including how many weeks (of the previous 5 weeks) in which this score was below 50.

In python, I think this could be done with a foor loop but I dont know how could I get that result in Dax. Any help? Thank you so much!!

[Ashish_Mathur]

Hi,

Do you have a Date column.  If yes, then build a Calendar Table and ensure that there is a Week number column in the Calendar Table.  After you have done this, share the download link of the PBI file.

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Table = DISTINCT('Fortable'[date])

2. Create measure.

Flag =
var _select=SELECTEDVALUE('Table'[date])
return
IF(
    WEEKNUM(MAX('Fortable'[date]),1)<WEEKNUM(_select,1)&&WEEKNUM(MAX('Fortable'[date]),1)>=WEEKNUM(_select,1) -7 ,1,0)

Sum =
var _select=SELECTEDVALUE('Table'[date])
var _sum=CALCULATE(SUM('Fortable'[score]),FILTER(ALLSELECTED(Fortable),WEEKNUM('Fortable'[date],1)<=WEEKNUM(_select,1)&&WEEKNUM('Fortable'[date],1)>=WEEKNUM(_select,1) -7&&'Fortable'[week]=MAX('Fortable'[week])))
return
_sum

weeks_measure =
var _select=SELECTEDVALUE('Table'[date])
var _sum=CALCULATE(SUM('Fortable'[score]),FILTER(ALLSELECTED(Fortable),WEEKNUM('Fortable'[date],1)<=WEEKNUM(_select,1)&&WEEKNUM('Fortable'[date],1)>=WEEKNUM(_select,1) -7&&'Fortable'[week]=MAX('Fortable'[week])))
return
CALCULATE(DISTINCTCOUNT('Fortable'[week]),FILTER( ALLSELECTED('Fortable'),[Sum]>50))

Measure =
var _select=SELECTEDVALUE('Table'[date])
var _sum=CALCULATE(SUM('Fortable'[score]),FILTER(ALLSELECTED(Fortable),WEEKNUM('Fortable'[date],1)<=WEEKNUM(_select,1)&&WEEKNUM('Fortable'[date],1)>=WEEKNUM(_select,1) -7&&'Fortable'[week]=MAX('Fortable'[week])))
var _if=if(
_sum>50,"Yes","No")
var _table=SUMMARIZE(ALLSELECTED(Fortable),'Fortable'[date],"1",CALCULATE(SUM('Fortable'[score]),FILTER(ALLSELECTED(Fortable),WEEKNUM('Fortable'[date],1)<=WEEKNUM(_select,1)&&WEEKNUM('Fortable'[date],1)>=WEEKNUM(_select,1) -7&&'Fortable'[week]=MAX('Fortable'[week]))))
return
IF(
COUNTROWS(FILTER(_table,[1]>=50))>1,"Yes","no")

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@Anonymous , You can do that with help from earlier

example

countx(filter(Table, Table[Week] >= earlier([Week]) -5  && Table[Week] <= earlier([Week])  && [Score] <50) , [Score])

refer: https://www.goodly.co.in/earlier-function-dax-power-bi/

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.