Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
samsha786
Helper I
Helper I

DAX NATURALINNERJOIN

My code:

Resource Allocations =
VAR AssignedCapacityWeighted =
                            SELECTCOLUMNS(
                                SUMMARIZECOLUMNS(
                                    'Project Resources'[ResourceId],
                                    'Project Resources',
                                    "Assigned Capacity Weighted",
                                    SUMX('Project Resources', ((DATEDIFF('Project Resources'[Assigned Start Date], 'Project Resources'[Assigned End Date], DAY)) * 'Project Resources'[Assigned Capacity]) / 365.0)),
                                "Id", 'Project Resources'[ResourceId],
                                "Assigned Capacity Weighted", [Assigned Capacity Weighted]
                            )
VAR ChapterTeamsFullName =
                            SUMMARIZECOLUMNS(
                            'Teams (People)'[Id],
                            'Teams (People)'[Chapter],
                            'Teams (People)'[Team],
                            'Teams (People)'[Full Name])
VAR ResourceAllocationParts =
                            NATURALINNERJOIN(ChapterTeamsFullName, AssignedCapacityWeighted)
RETURN ResourceAllocationParts
 
No common join columns detected... why doesn't it know that ResourceId renamed to Id is the same as Id from the two tables I created? What do I need to do to make it understand this. In my existing models relationships have already been created.
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

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:
Picture.png
 
Expecting a single record with Assigned Weighted Capacity = 95%. What am I missing? If I don't group by Year then it works perfectly.
Greg_Deckler
Super User
Super User

That function can be rather tempermental. Would need source data to try to replicate:

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.