Forum Discussion
samsha786
Helper I
Helper IDAX NATURALINNERJOIN
My code: Resource Allocations = VAR AssignedCapacityWeighted = SELECTCOLUMNS( SUMMARIZECOLUMNS( ...
NATURALINNERJOIN as per documentation
Columns being joined on must have the same data type in both tables. Only columns from the same source table (have the same lineage) are joined on. For example, Products[ProductID], WebSales[ProductdID], StoreSales[ProductdID] with many-to-one relationships between WebSales and StoreSales and the Products table based on the ProductID column, WebSales and StoreSales tables are joined on [ProductID].https://docs.microsoft.com/en-us/dax/naturalinnerjoin-function-dax
Refer how to use : https://www.youtube.com/watch?v=mcQ_ZmuWvDs&vl=en
there is a costly option
filter(crossjoin(table1,table2), Table1[A]=Table2[B])
Try in edit query
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
amitchandak
Super User
Super UserNATURALINNERJOIN as per documentation
Columns being joined on must have the same data type in both tables.
Only columns from the same source table (have the same lineage) are joined on. For example, Products[ProductID], WebSales[ProductdID], StoreSales[ProductdID] with many-to-one relationships between WebSales and StoreSales and the Products table based on the ProductID column, WebSales and StoreSales tables are joined on [ProductID].
https://docs.microsoft.com/en-us/dax/naturalinnerjoin-function-dax
Refer how to use : https://www.youtube.com/watch?v=mcQ_ZmuWvDs&vl=en
there is a costly option
filter(crossjoin(table1,table2), Table1[A]=Table2[B])
Try in edit query
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
samsha786Helper I
Helper IOk, So I made it a step further... almost what I need, code below:
Resource Allocations =
VAR AssignedCapacityWeighted =
SELECTCOLUMNS(
SUMMARIZECOLUMNS(
'Project Resources'[ResourceId], 'Project Resources'[Assigned End Date].[Year], 'Project Resources',
"Assigned Capacity Weighted", SUM('Project Resources'[Assigned Capacity Weighted]), "Year", MIN('Project Resources'[Assigned End Date].[Year])
),
"ResourceId", 'Project Resources'[ResourceId] & " ",
"Assigned Capacity Weighted", [Assigned Capacity Weighted], "Year", [Year]
)
VAR ChapterTeamsFullName =
SELECTCOLUMNS('Teams (People)', "Id", 'Teams (People)'[Id],
"ResourceId", 'Teams (People)'[Id] & " ",
"Chapter", 'Teams (People)'[Chapter],
"Team", 'Teams (People)'[Team],
"Full Name", 'Teams (People)'[Full Name])
VAR ResourceAllocationParts =
NATURALLEFTOUTERJOIN(ChapterTeamsFullName, AssignedCapacityWeighted)
RETURN ResourceAllocationParts
Output:
Expecting a single record with Assigned Weighted Capacity = 95%. What am I missing? If I don't group by Year then it works perfectly.