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 ,

 

Since I don't have access to your data source, I can't see your raw data.
To optimize report performance, you can create a measure instead of a calculated column.
If your Flag1 and Flag2 columns are in the same table, create the following measure:

 

FastBreak_TOV = IF(MAX('Table'[Flag1])=1 && MAX('Table'[Flag2])=1 ,1,0)

 

If your Flag1 and Flag2 columns are in different tables, make sure they have a related column such as an id column （Don't build relationships） and create the following measure:

 

Flag2 = CALCULATE(MAX('Table 2'[Flag2]),FILTER(ALL('Table 2'),'Table 2'[id]=MAX('Table 1'[id])))
_FastBreak_TOV = 
var _1=MAX('Table 1'[Flag1])
var _2=[Flag2]
return IF(_1=1 && _2=1 ,1 ,0)

 

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 

     

    Here, I add you the Google Drive folder link to the pbix file and the raw data: https://drive.google.com/drive/folders/1BxVWCtoHkcQYnDfOpqmYFpFgXpoEIPOt?usp=sharing 

     

    Flag 1 and Flag 2 are columns that don't exist in raw data. In fact, their creation was the first two steps I take in my process. Hope this can help you. For me, it could be more effective if I can have the FastBreak_TOV flag as a column instead of measure, to identify when a row is marked with that flag.

     

    Thank you for your time and patience.