cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver I
Resolver I

'RELATED' function in an IF Statement

Hi everyone,

<Message deleted>

Thanks for any help!

3 ACCEPTED SOLUTIONS
Super User IV
Super User IV

I doubt usage need to change to =

Refer https://databear.com/dax-related-table-functions/

Is it a measure or columns ?

Try like

Working Hours = IF('Main'[Location]= RELATED(Indianlocations[Location]),9,IF('Main'[Location] = RELATED(Otherlocations[Location]),8,0))

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Super User III
Super User III

Hi @DevadathanK ,

 

IN operator in Power BI returns either TRUE/FALSE value. So, I don't think it is used correctly here.

 

Try modifying your DAX as follows:

Working Hours = IF(

                                 'Main'[Location] == RELATED(Indianlocations[Location]), 9, 

                                  IF('Main'[Location] == RELATED(Otherlocations[Location]), 8, 0)

                               )

 

In case you want to use IN operator, try following DAX:

Working Hours = IF(

                                 'Main'[Location] IN RELATED(Indianlocations[Location]) = TRUE(), 9, 

                                  IF('Main'[Location] IN RELATED(Otherlocations[Location]) = TRUE(), 8, 0)

                               )

 

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

 

Thanks,

Pragati

 





If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




View solution in original post

Community Support
Community Support

@DevadathanK 

 

The problem is you can only use table expression after IN, 

Try replace RELATED() with RELATEDTABLE() or VALUES():

 

Working Hours = IF('Main'[Location]IN RELATEDTABLE(Indianlocations),9, IF('Main'[Location] IN RELATEDTABLE(Otherlocations),8,0))
Working Hours = IF('Main'[Location]IN VALUES(Indianlocations[Location]),9,IF('Main'[Location] IN VALUES(Otherlocations[Location),8,0)))))

 

 

Paul Zheng
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

7 REPLIES 7
Community Support
Community Support

@DevadathanK 

 

The problem is you can only use table expression after IN, 

Try replace RELATED() with RELATEDTABLE() or VALUES():

 

Working Hours = IF('Main'[Location]IN RELATEDTABLE(Indianlocations),9, IF('Main'[Location] IN RELATEDTABLE(Otherlocations),8,0))
Working Hours = IF('Main'[Location]IN VALUES(Indianlocations[Location]),9,IF('Main'[Location] IN VALUES(Otherlocations[Location),8,0)))))

 

 

Paul Zheng
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

Hi @V-pazhen-msft 

 

I see. Thank you for helping me out 🙂

 

I tried out both of your suggestions and i got it to work by using VALUES(), but i faced another error whilst using RELATEDTABLE().

 

 

 

 

 

 

Super User III
Super User III

Hi @DevadathanK ,

 

IN operator in Power BI returns either TRUE/FALSE value. So, I don't think it is used correctly here.

 

Try modifying your DAX as follows:

Working Hours = IF(

                                 'Main'[Location] == RELATED(Indianlocations[Location]), 9, 

                                  IF('Main'[Location] == RELATED(Otherlocations[Location]), 8, 0)

                               )

 

In case you want to use IN operator, try following DAX:

Working Hours = IF(

                                 'Main'[Location] IN RELATED(Indianlocations[Location]) = TRUE(), 9, 

                                  IF('Main'[Location] IN RELATED(Otherlocations[Location]) = TRUE(), 8, 0)

                               )

 

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

 

Thanks,

Pragati

 





If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




View solution in original post

Hello @Pragati11 

 

Thank you for helping me out 🙂 I tried out both of your suggestions and replacing 'IN' with '==' works perfectly. However, whilst trying out the 2nd method (using True() ) i faced an error.

Super User IV
Super User IV

I doubt usage need to change to =

Refer https://databear.com/dax-related-table-functions/

Is it a measure or columns ?

Try like

Working Hours = IF('Main'[Location]= RELATED(Indianlocations[Location]),9,IF('Main'[Location] = RELATED(Otherlocations[Location]),8,0))

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Hi

 

It's a Column and i tired out your suggestion of replacing the 'IN' with '='. It works perfectly now.

 

Thank you so much!

Super User IV
Super User IV

Hmm, that's odd, do you actually have relationships defined to those other tables?

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors