Create a column by formula IF

[neonguyen]

I have two tables as bellow:

Deal History:

Deal:

Two tables have relationship by Deal History[Deal ID] = Deal[ID]

And i want to create a column to know which deal have history = PT Pending.

I try with formula: 

Check PT =

IF(CONTAINS('Deal History','Deal History'[StageName],"PT Pending"),1,0)

And fail... every results is 1.

In this case, i expect that will be as below:

Thank you when you read my article

[neonguyen]

@Anonymous  @ZunzunUOC both get same result. 

And with my formula, if i change to : 

IF(CONTAINS(RELATEDTABLE('Deal History'),'Deal History'[Stage_change,"PT Pending"),1,0)

Will get same results.

[Anonymous]

@neonguyen ,

Try this instead,

Check = IF(CONTAINS(FILTER('Deal History','Deal History'[Deal ID] = Deal[ID]),'Deal History'[Stage_change], "PT Pending"), 1, 0)

This just filters the table down to one deal at a time, and then checks to see if PT Pending Exists in the Stage_change column, I'll provide pictures. DON'T forget to check your relationships, they matter here.

Hope this helps,

Testing Tech

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

[Kalpavruksh]

Hi,

We believe the reason your logic fails is because the relationship between two tables is defined by a column of Deal ID in the Deal History table, which has multiple values i.e. multiple rows for Deal 1.

Kalpavruksh Technologies | Microsoft Gold Partner
Denmark | USA | India | Germany

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

[ZunzunUOC · Accepted solution]

Hi @neonguyen , try with this code:

Check PT = IF(CALCULATE(COUNTROWS('Deal History');FILTER('Deal History';'Deal History'[Deal ID]=Deal[ID] && 'Deal History'[Stage_change]="PT Pending"))<>0;1;0)

Remenber to change ";" per "," and disabled the count option.

Best Regards,
Miguel

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