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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DevadathanK
Resolver I
Resolver I

'RELATED' function in an IF Statement

Hi everyone,

<Message deleted>

Thanks for any help!

3 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

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

 

View solution in original post

Pragati11
Super User
Super User

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

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

View solution in original post

V-pazhen-msft
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
V-pazhen-msft
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.

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().

 

 

 

 

 

 

Pragati11
Super User
Super User

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

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

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.

amitchandak
Super User
Super User

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

 

Hi

 

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

 

Thank you so much!

Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.