Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
jpbi23
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

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:

vjunyantmsft_0-1713167928589.png

vjunyantmsft_2-1713168236759.png

vjunyantmsft_3-1713168678607.png

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

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

vjunyantmsft_4-1713168770851.png


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.

View solution in original post

4 REPLIES 4
Idrissshatila
Super User
Super User

Hello @jpbi23 ,

 

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




jpbi23
Frequent Visitor

opportunity is (one relationship)

Forms is (many relationships) 

 

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

 

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:

vjunyantmsft_0-1713167928589.png

vjunyantmsft_2-1713168236759.png

vjunyantmsft_3-1713168678607.png

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

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

vjunyantmsft_4-1713168770851.png


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
Continued Contributor
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

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.