Forum Discussion

RYU209's avatar
RYU209
Icon for Helper III rankHelper III
3 years ago
Solved

Check if a value exists between many-to-tables in Direct Query

Hello, I have two Direct Query tables with a Many-To-Many relationship (Table1 and Table2). They are connected by a key that is made up of a concatenated Transaction # and Order #.  I have a Ta...
  • johnt75's avatar
    johnt75
    3 years ago

    You could create a measure like

    Value is in Table1 =
VAR CurrentKey =
    SELECTEDVALUE ( 'Table2'[concatenated key] )
RETURN
    IF ( CurrentKey IN ALL ( 'Table1'[concatenated key] ), 1 )

    and then use that as a visual level filter to only show when the value is 1

johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

You could create a measure like

Value is in Table1 =
VAR CurrentKey =
    SELECTEDVALUE ( 'Table2'[concatenated key] )
RETURN
    IF ( CurrentKey IN ALL ( 'Table1'[concatenated key] ), 1 )

and then use that as a visual level filter to only show when the value is 1

  • RYU209's avatar
    RYU209
    Icon for Helper III rankHelper III
    3 years ago

    johnt75 That worked perfectly! 

    Could you explain what IN and ALL are doing so I can understand how it works? 

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      3 years ago

      ALL returns all the values of Table 1 key regardless of any filters which may be applied, including any filters from the many-to-many relationship.

      IN then checks to see whether the current key exists in those values.

      • RYU209's avatar
        RYU209
        Icon for Helper III rankHelper III
        3 years ago

        johnt75 Thank you!