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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Sarvina
Frequent Visitor

Issue with left outer join

i have a table with start date , end date , name , allocation and only the month for which allocation is given.Capacity is 1 for all (created new column with 1 a value).

Sarvina_0-1632126711710.png

I created one table using below dax expression to grt extra rows for months between start date and end date.

table code:SUMMARIZE(filter(CROSSJOIN(Sheet1,'Date'),'Date'[Date]>=(Sheet1[startdate]) && 'Date'[Date]<=(Sheet1[end date])), [Month Year],[Month Year Sort],Sheet1[name],sheet1[empid])

 

Sarvina_1-1632126880794.png


Now i want to join these tables. Emp id is the matching column in both table but I am unable to do left outer join between table and allocation table. i am getting error "No common column joins detected".

 

Can anyone help me ?

10 REPLIES 10
Sarvina
Frequent Visitor

I am unable to attach the pbix file. I dont see option to attach.

Sarvina_0-1632144899150.png

 

Sarvina
Frequent Visitor

Thanks selimovd and Amit for reply.

 

Yes I am using DAX for left outer join.

Sarvina_0-1632144134089.png

 

Hi @Sarvina

 

Maybe the many-to-many relationship is causing the issue, you can merge the two tables in Query Editor.

 

If you still have some question, please don't hesitate to let me known.‌‌

 

Best Regards,

Link

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

We cannot do this in power query because the table "Table" is created using dax. I cannot merge in power query. While using dax I first related the table using emp id , but it wasnt relating and same is the case with date column. How can i join the table using left outer join. Please find below  lin for pbix file.

https://www.dropbox.com/t/M9HO0EgGz5heimhO

 

The table is created using dax. I cannot view that table in query editor. As per my understanding the join has to be done using dax only

selimovd
Super User
Super User

Hey @Sarvina ,

 

how do you want to join them?

As you're in DAX can you post the code you used for the join?

 

I personally would do the transformation in Power Query as it's part of the ETL process.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Please find the sample pbix file in below link

https://www.dropbox.com/t/M9HO0EgGz5heimhO

 

amitchandak
Super User
Super User

@Sarvina , you are trying dax left outer join ?

You should be able join sheet1 and new table, Just make sure ID column name is same

 

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

 

Can you share a sample pbix after removing sensitive data.

Please find the sample pbix file in below link

https://www.dropbox.com/t/M9HO0EgGz5heimhO

 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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