Issue with left outer join

[Sarvina]

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).

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])

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 ?

[Sarvina]

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

[Sarvina]

Thanks selimovd and Amit for reply.

Yes I am using DAX for left outer join.

[Sarvina]

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

[v-xulin-mstf]

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!

[Sarvina]

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

[Sarvina]

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]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Sarvina]

Please find the sample pbix file in below link

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

[amitchandak]

@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.

[Sarvina]

Please find the sample pbix file in below link

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