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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @Anonymous ,

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

View solution in original post

7 REPLIES 7
jaideepnema
Solution Sage
Solution Sage

Hi @Anonymous ,

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 😀

Anonymous
Not applicable

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 @Anonymous ,

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 😀

 

Anonymous
Not applicable

Thanks Jaideep

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

Hi @Anonymous ,

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

jaideepnema
Solution Sage
Solution Sage

Hi @Anonymous ,

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

Anonymous
Not applicable

Hi Jaideep,

Here is the link to download the file.

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

Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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