'RELATED' function in an IF Statement

[DevadathanK]

Hi everyone,

<Message deleted>

Thanks for any help!

[amitchandak · Accepted solution]

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

[Pragati11 · Accepted solution]

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

[Anonymous · Accepted solution]

@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.

[DevadathanK]

Hi @Anonymous 

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 · Accepted solution]

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

[DevadathanK]

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 · Accepted solution]

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

[DevadathanK]

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]

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