Calculate Flag based on conditions

[sfernamer]

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 happen, I had to do some steps:

- Create a column named Flag1 where, if the [Situation] field match with a specific value, it's a 1 else 0.

- Create a column named Flag2 where, if the [Final Result] field match with a specific value, it's a 1 else 0.

* Here, I need that Flag 1 (if marked as 1, it's in the line below) in the same row than Flag2 (to have both 1s in the same row), so I add new 3 steps:

- Create a new index column from 1 (instead of the existing one, created from 0).

- Merge the table with itself using the index columns as keys, doing a LEFT JOIN.

- After this step, I expand only the Flag1 field. After the merge, I get both flags in the same row and can create the new column [FastBreak_TOV] with a filter (if both Flags are 1, then 1 else 0).

This solution worked but, in terms of performance, is far from being perfect if there are many rows. Is there any way in DAX to create the [FastBreak_TOV]?

Add the pbix file to help: https://drive.google.com/file/d/1t4K-6IULIRMpsl0Gxc7kw5AaJCQosAO1/view?usp=drive_link 

Thank you for your time. 

[Anonymous · Accepted solution]

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. 

[sfernamer]

Hi @Anonymous 

Thank you for your time. This last solution worked perfectly (and only adding one column). Thta was perfect! 

Thank you again. 🙂

[Anonymous]

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]

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!

[Anonymous]

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]

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.