Forum Discussion

neonguyen's avatar
neonguyen
Frequent Visitor
7 years ago
Solved

Create a column by formula IF

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
Need Help
  • ZunzunUOC's avatar
    ZunzunUOC
    7 years ago

    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.

4 Replies

  • ZunzunUOC's avatar
    ZunzunUOC
    Resolver III
    7 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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.
  • neonguyen's avatar
    neonguyen
    Frequent Visitor
    7 years ago

    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.
  • Kalpavruksh's avatar
    Kalpavruksh
    Resolver I
    7 years ago
    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.