Display rows where foreign key is present in PowerBI

Question

I have two tables. The first table is called Capability: Capability Table

The second table is called Information Group:

Some ID's in the Capability table have been referenced in the Information Group table (i.e. in the Capability column)

At present, the query that powers the dashboard for the Capability view is:

= Source{[Schema="dbo",Item="Capability"]}[Data]

I want to show only rows in the Capability table where there is at least one reference in the Information Group table.

In other words, since Capability ID 10 is not referenced in Information Group, the Capability table should exclude that row in the output (i.e. shows only 1 to 9). Desired output

|------------------------------------------------------------------|

Effort made so far:

I have managed to select distinct Capability ID's in the Information Group table via this query: = Table.SelectColumns(Table.Distinct(Source{[Schema="dbo",Item="Information_Group"]}[Data], "Capability"), "Capability"), which produces this:

Output

The next step that I would like to do is to check the ID column of the Capability table against the list above and display only rows where the values are contained in the table. Something like this:

= Table.SelectRows(Table.Contains(Source{[Schema="dbo",Item="Capability"]}[Data], each[ID] is contained in the list))

I have read up MSDN on using Table.Contains, but I cannot bridge the gap to get to where I want.

Please let me know if clarification is needed. Thank you.

[Edit] Answer that I am satisfied with:

let
    Source = Sql.Database("server\database", "Mclaren"),
    dbo_Capability = Table.NestedJoin(
        Source{[Schema="dbo",Item="Capability"]}[Data],{"ID"},
        Source{[Schema="dbo",Item="Information_Group"]}[Data],{"Capability"},        
        "NewColumn",
        JoinKind.RightOuter
    ),
    #"Removed Columns" = Table.RemoveColumns(dbo_Capability,{"NewColumn"})
in
    #"Removed Columns"

Foxan Ng Foxan Ng · Accepted Answer · 2017-02-28T04:17:24

A way to achieve so in DAX is to create a table using FILTER and CONTAINS:

Filtered Capability = 
FILTER(
    Capability,
    CONTAINS(
        'Information Group',
        'Information Group'[Capability],
        Capability[ID]
    )
)

Or with a even simpler syntax using IN which is introduced since Nov 2016:

Filtered Capability = 
FILTER(
    Capability,
    Capability[ID] IN VALUES('Information Group'[Capability])
)

Result will be as follows:

Display rows where foreign key is present in PowerBI

2 Answers