Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

For loop in power bi

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!!

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    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.