Forum Discussion

jpbi23's avatar
jpbi23
Helper I
2 years ago
Solved

using two calculated columns in if statement

I have two tables 

 

Forms & Opportunity

 

I have tables (Opportunity & Forms) with one to many relationship. I also have two dax calulated columns in each table. 

 

Calculated Field in Opportunity - Spotlight

Calculated Field in Forms - POV

 

I need new calculated column with in opportunity table with this logic:

 

newColumn = if 'Opportunity'(spotlight) = 'Yes' && 'Forms'(POV) = 'No' then 'True' 

 

but when I write the dax, Forms table doesn't show up in dax. Any ideas?

 

Thanks,

Jay

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi jpbi23 ,

    Idrissshatila Thanks for your concern about this case!
    The reason you can't find the table Forms when you create a calculated column in the Opportunity table is because the Opportunity table is on the "one" side. After my test, you can't find the Forms table even with the RELATED function.
    I don't quite understand, your table Opportunity and table Forms are in a one-to-many relationship and Opportunity is on the "one" side. You need to create the calculated column in Opportunity, which is impossible because columns in the "many" side of the table cannot be applied to columns in the "one" side of the table, only columns in the "one" side of the table can be applied to the "many" side..
    Here are my sample datasets:

    You can only use this DAX in the Forms table to achieve this:

    Column = 
IF(
    'Forms'[POV] = "No" && RELATED(Opportunity[Spotlight]) = "Yes",
    "True",
    "False"
)


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

4 Replies

  • quantumudit's avatar
    quantumudit
    Super User
    2 years ago

    Hi jpbi23 
    It would be great if you could provide two sample tables, i.e., for "Opportunity" and "Form" and also let us know the nature of the relationship, i.e., which table is on one side and which one is on the many side (providing a nice anonymized sample data would be of great help).

    Best Regards,
    Udit

    🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
    ✨ Visit My Linktree: LinkTree

  • jpbi23's avatar
    jpbi23
    Helper I
    2 years ago

    opportunity is (one relationship)

    Forms is (many relationships) 

     

    both joined on opportunity id key. One opportunity can have multiple forms. 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi jpbi23 ,

      Idrissshatila Thanks for your concern about this case!
      The reason you can't find the table Forms when you create a calculated column in the Opportunity table is because the Opportunity table is on the "one" side. After my test, you can't find the Forms table even with the RELATED function.
      I don't quite understand, your table Opportunity and table Forms are in a one-to-many relationship and Opportunity is on the "one" side. You need to create the calculated column in Opportunity, which is impossible because columns in the "many" side of the table cannot be applied to columns in the "one" side of the table, only columns in the "one" side of the table can be applied to the "many" side..
      Here are my sample datasets:

      You can only use this DAX in the Forms table to achieve this:

      Column = 
IF(
    'Forms'[POV] = "No" && RELATED(Opportunity[Spotlight]) = "Yes",
    "True",
    "False"
)


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