cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## 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

1 ACCEPTED SOLUTION
Community Support

Hi @jpbi23 ,

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 4
Super User

Hello @jpbi23 ,

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

 Vote for my Community Mobile App Idea

Proud to be a Super User!

Frequent Visitor

opportunity is (one relationship)

Forms is (many relationships)

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

Community Support

Hi @jpbi23 ,

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.

Continued Contributor

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