Forum Discussion

sfernamer's avatar
sfernamer
Icon for Helper III rankHelper III
3 years ago
Solved

Calculate Flag based on conditions

Hi everyone!   I'm working with data and need to create a flag with a specific situation happens (that implies to analyze 2 consecutive rows bearing in mind I have an index column). To make it happ...
"CALCULATE"
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi sfernamer ，

     

    By nature, columns created in power bi desktop cannot be used in power query.

    We can create a column.

    _FastBreak_TOV = var a=ADDCOLUMNS('Table',"flag1s",IF('Table'[Final Result]="Turnover",1,0),"flag2s",IF('Table'[Situation]="FastBreak",1,0))
var b=ADDCOLUMNS(a,"flag3s",MAXX(FILTER(a,[Índice]=EARLIER('Table'[Índice])+1),[flag1s]))
var c=FILTER(b,[Índice]=EARLIER('Table'[Índice]))
return IF(MAXX(c,[flag2s])*MAXX(c,[flag3s])=1,1,0)

     

    If you do not want to remove the extra calculated columns, you can use the SELECTCOLUMNS function to create a table containing the columns you need.

    Table 2 = SELECTCOLUMNS('Table',"indice",'Table'[Índice],"sit",'Table'[Situation],"final",'Table'[Final Result],"tov",'Table'[FastBreak_TOV])

     

    Best Regards,

    Neeko Tang

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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi sfernamer ,

 

According to your description, here are my steps you can follow as a solution.

(1)My test data is the same as yours.

(2) We can create calculated columns.

Flag1 = IF('Table'[Final Result]="Turnover",1,0) 
Flag2 = IF('Table'[Situation]="FastBreak",1,0) 
Rank = RANKX('Table','Table'[Índice],,ASC,Dense)
Rank2 = [Rank]-1 
Flag3 = CALCULATE(SUM('Table'[Flag1]),FILTER('Table','Table'[Rank2]=EARLIER('Table'[Rank])))
FastBreak_TOV = IF('Table'[Flag2]=1 && 'Table'[Flag3]=1 ,1,0)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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

sfernamer's avatar
sfernamer
Icon for Helper III rankHelper III
3 years ago

Hi Anonymous 

 

Thanks for your reply! Looks like it's working! I would like to ask if it's possible to do it in Power Query instead as calcualted columns. The reason is quite simple. Doing it in Power Query, I could, later, delete the auxiliar flags and only have a column with the final result [FastBreak_TOV].

 

Could you tell me, please, if it's possible?

 

Thank you again for your time and patience!