cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amitkumar93
Helper II
Helper II

Right Join in Power bi Tabular view

Hi Team,

I have two sample tables here named Cust and Invoice

amitkumar93_0-1622615793463.png

the relationship in power bi desktop is many to many

Now i want to create a tabular view with Date, Custid, Cust name, invoice amount, here i want to filter the date as 15.1.2021, 16.01.2021 and 17.01.2021 and want to see the invoice amount 
As you can see in above table that the custid of above dates are not available in Cust table. however i want to see the invoice amt with custid as blank 
i am getting a blank tabular view.
when i select show items with no data i am getting this result

amitkumar93_1-1622616181822.png
Invoice amt total is correct but i have no data for individual date,
is it possble anyhow to see invoice amt for individual dates when the data is not in first table ??

 




 

1 ACCEPTED SOLUTION

Hi @amitkumar93 ,

As per my understanding the only way to achieve this is through Merging.

View solution in original post

7 REPLIES 7
jaideepnema
Super User
Super User

Hi @amitkumar93 ,

The Invoice Amount cannot be shown as the corresponding customer ID are not available in the Customer Table. In your case for the three dates ie: 15th,16th and 17th January 2021 the customer IDs 16,17 and 18 are not available in the Customer Table. Since these are not available you can only show them as blank. 

 

jaideepnema_0-1622618100747.png

 

Please accept this as a solution if your question has been answered !!

Appreciate a Kudos 😀

Exactly i want to show them as blanks in the tabular view 

the output i want is 

amitkumar93_0-1622620761562.png



is this possible to show in power bi??

Hi @amitkumar93 ,

You can merge the two tables as shown below and then expand the columns required from the merged table:

jaideepnema_1-1622622193537.png

Use this merged table to create the table required.

jaideepnema_2-1622622299386.png

Please accept this as a solution if your question has been answered !!

Appreciate a Kudos 😀

 

Thanks Jaideep

seems like merging is the only way, is not it ?

Hi @amitkumar93 ,

As per my understanding the only way to achieve this is through Merging.

View solution in original post

jaideepnema
Super User
Super User

Hi @amitkumar93 ,

Can you share a PBIX file with the sample data if possible ?

Hi Jaideep,

Here is the link to download the file.

https://www.dropbox.com/s/ecw0ogej10qvmr1/Samplefile.pbix?dl=0

Thanks

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!