Check for matching group in two separate tables

[ddorhout]

I have a new problem that I am facing, that seems to be similar to the previous topic.

I have one main table in which I have an overview of all the users and the items that they need.

There are two reference tables:

• One that states to what group users are allocated to
• Users can be part of multiple groups
• One that states to what group the different items are allocated to
• Items can be part of multiple groups

The main question now is: in what cases is there a match between the groups of a user and the groups of the item that is needed?

See an example of the result column that is needed below:

Table 1

Column 1	Column 2	Result column
User 1	Item 1	Yes
User 1	Item 2	No
User 1	Item 3	No
User 2	Item 1	Yes
User 2	Item 2	Yes
User 2	Item 3	Yes

Table 2

Column X	Column Y
User 1	Group X
User 1	Group Y
User 1	Group Z
User 2	Group X
User 2	Group A

Table 3

Column A	Column B
Item 1	Group X
Item 1	Group Y
Item 2	Group A
Item 2	Group B
Item 3	Group A

Thanks in advance!

[selimovd · Accepted solution]

Hey @ddorhout ,

that is possible.

Please check the following calculated column. I wrote the comments what exactly is going on in the formula:

Result = 
-- the User of the current row
VAR vUserCurrentRow = 'Table 1'[Column 1]

-- the Item of the current row
VAR vItemCurrentRow = 'Table 1'[Column 2]

-- Filter of Table 2 to the current user and selection of the related groups
VAR vTableGroupUser =
    SELECTCOLUMNS(
        FILTER(
            'Table 2',
            'Table 2'[Column X] = vUserCurrentRow
        ),
        "@Group", 'Table 2'[Column Y]
    )

-- Filter of Table 3 to the current item and selection of the related groups
VAR vTableGroupItem =
    SELECTCOLUMNS(
        FILTER(
            'Table 3',
            'Table 3'[Column A] = vItemCurrentRow
        ),
        "@Group", 'Table 3'[Column B]
    )

-- Intersection of the two tables. If there is a group in table 2 for the current user
-- and group in table 3 for the current item we find a match here
VAR vIntersection =
    INTERSECT(
        vTableGroupUser,
        vTableGroupItem
    )

-- we count if there is a match / if one row is left after the intersection
VAR vAmountRowsIntersection =
    COUNTROWS( vIntersection )

RETURN

-- If there is row we return "Yes", otherwiese "no"
    IF(
        vAmountRowsIntersection > 0,
        "Yes",
        "No"
    )

The result is identical to your desired result:

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[ddorhout]

I really liked your dummy proof explanation! Keep up the good work!

[selimovd]

@ddorhout haha, I'm happy you liked it 😉

Yeah, I think it makes more sense when I explain what's going on than just throwing a huge formula to you.

Have a great day and best regards

Denis

[CNENFRNL]

PQ solution,

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLVIwVNJR8ixJzQUyYnXQxYywiBkjxIyw6DXCotcISW8sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLVIwVNJRci/KLy1QiFCK1cEQjMQmGIUQNMKmHSHoqBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column X" = _t, #"Column Y" = _t]),
    Table3 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzVUwVNJRci/KLy1QiFCK1cEQjEQIGsEFHbEJOiEEjZFVxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t]),

    Merge23 = Table.NestedJoin(Table2, "Column Y", Table3, "Column B", "m", JoinKind.LeftOuter),
    #"Expanded m" = Table.RemoveColumns(Table.ExpandTableColumn(Merge23, "m", {"Column A"}, {"Column A"}), "Column Y"),
    
    Merged = Table.NestedJoin(Table1, {"Column 1", "Column 2"}, #"Expanded m", {"Column X", "Column A"}, "Result_PQ", JoinKind.LeftOuter),
    Res_PQ = Table.TransformColumns(Merged, {"Result_PQ", each if Table.RowCount(_)>0 then "Yes" else "No"})
in
    Res_PQ

Calculated column solution with DAX