using two calculated columns in if statement

[jpbi23]

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

[Idrissshatila]

Hello @jpbi23 ,

try to add "Related" function before this  'Forms'(POV) 

[jpbi23]

opportunity is (one relationship)

Forms is (many relationships) 

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

[v-junyant-msft · Accepted solution]

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.

[quantumudit]

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