Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
Using PBIRS, Sept, 2021.
I am trying to generate a list of needed parts for orders that have been shipped.
The shipping table has a list of modules that have been shipped and are en route.
There may be more than 1 of the same module that has been shipped.
I created a dim table with unique module_id's and module_names
I created relationships to the other 2 tables so I have 2 1:M relationships
I added a Table Visual to the Report Canvas and added the Shipped[Module_id]
I tried to add the ListOfParts[Part_id] but I get a "Fix this" error.
How can I get a list of parts for each of the en-route modules?
I cannot create M:M bi-directional relationship.
The pbix file is available here.
Thanks,
-w
A module has a list of parts. This is given in ListOfParts Table.
The 2 tables contain mutliple instances
Solved! Go to Solution.
I was either overthinking it or not thinking about it clearly.
In the end, it was a simple merge query.
For module 10, I had 3 en-route and each module has 3 parts so I expecteded 9 rows in the merge query, which is exactly what I have.
let
Source = Table.NestedJoin(Shipped, {"Module_id"}, ListOfParts, {"Module_id"}, "ListOfParts", JoinKind.LeftOuter),
#"Expanded ListOfParts" = Table.ExpandTableColumn(Source, "ListOfParts", {"Part_id", "Part_Name"}, {"Part_id", "Part_Name"})
in
#"Expanded ListOfParts"
I was either overthinking it or not thinking about it clearly.
In the end, it was a simple merge query.
For module 10, I had 3 en-route and each module has 3 parts so I expecteded 9 rows in the merge query, which is exactly what I have.
let
Source = Table.NestedJoin(Shipped, {"Module_id"}, ListOfParts, {"Module_id"}, "ListOfParts", JoinKind.LeftOuter),
#"Expanded ListOfParts" = Table.ExpandTableColumn(Source, "ListOfParts", {"Part_id", "Part_Name"}, {"Part_id", "Part_Name"})
in
#"Expanded ListOfParts"
Hi,
Can you create a measure like this, drag it to the table and see if it works?
Regards,
Kishore
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |