Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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!
Solved! Go to 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:
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:
I really liked your dummy proof explanation! Keep up the good work!
@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
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
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |