Forum Discussion

samsha786's avatar
samsha786
Icon for Helper I rankHelper I
6 years ago
Solved

DAX NATURALINNERJOIN

My code: Resource Allocations = VAR AssignedCapacityWeighted =                             SELECTCOLUMNS(                                 SUMMARIZECOLUMNS(                                     ...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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

  • samsha786's avatar
    samsha786
    Icon for Helper I rankHelper I
    6 years ago

    Ok, 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.
  • samsha786's avatar
    samsha786
    Icon for Helper I rankHelper I
    6 years ago

    Not a complete answer, but it was enough to get me moving in the right direction.