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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
suji
Advocate I
Advocate I

How to display Intersect and except data of two tables

Hi ,

 

Need help from you on below .

I have two table's A and B. There is a common column "id" in A and B.


Below is the sample data for ex,

A

id   Name  age
--------------------
1    aaa    10
2    bbb   15
3    ccc    16
4    ddd   20

 

id   Name   Dept
--------------------
2   bbb   D2
4   ddd   D4
5   eee    D5
6   fff      D6

 

i have to display
ID's in only A  (1,3)
ID's in only B  (5,6)
Intersect of id's in A and B. (2,4)

 

Please help how to achieve this and what is the best visual to show the output ?

 

 

Thanks & Regards,

Suresh Peddirsi

 

UnionIntersection.png

1 ACCEPTED SOLUTION

Hi Feldmann & Marcel,

 

Thanks for your quick response.

today i tried using Append queries option. I appended Table A and Table B and created a new table. After that i created a new custom column to know the record from which table.  It is working exactly what i want.

 

 

Thanks & Regards,

Suresh Peddirsi.

 

1.jpg2.jpg

View solution in original post

9 REPLIES 9
thiyageshwarans
Frequent Visitor

Hi All,

 

Need help from you on below

 

I have a table A. i want to compae the data with the table using  different provider.

 

Example 1

 

select the provider AA and BB in the slicer, output should show only 0001 and 0002 records.

 

Example 2

 

select the provider AA and CC in the slicer, output should show only 0001 records.

 

Example 3

 

select the provider BB and CC in the slicer, output should show only 0001 and 0004 records.

 

Below is the Sample Data for ex.

 

ID, Provider, Postal

--------------------

1   AA           0001

2   AA           0002

3   AA           0005

4   BB            0001

5   BB            0002

6   BB            0004

7   CC            0001

9   CC            0004

So you need a measure here. Check out the DAX-solution that you find in this thread (in the comments): https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/16317157-slicer-and-or-functional...

 

Imke Feldmann

www.TheBIccountant.com -- How to integrate M-code into your solution  -- Check out more PBI- learning resources here

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Thanks for your reply. it's working...

Anonymous
Not applicable

Hello, could you please share how did you use AND in the slicer. I could not get clear from the dax example.

 

Thanks.

This is how the formula is evaluated:

 

image.png

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ImkeF
Super User
Super User

In the query-editor, you merge your tables and adjust the join types accordingly:

 

PBI_JoinKinds.jpg

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi Feldmann & Marcel,

 

Thanks for your quick response.

today i tried using Append queries option. I appended Table A and Table B and created a new table. After that i created a new custom column to know the record from which table.  It is working exactly what i want.

 

 

Thanks & Regards,

Suresh Peddirsi.

 

1.jpg2.jpg

Hi @suji,

It's amazing you have resolved your problem by yourself. Please mark your solution as answer, and more people will get helpful workaround from it.

Thanks,
Angelia

MarcelBeug
Community Champion
Community Champion

It looks like you are looking for a Venn diagram.

It is not avaialble, but you can vote for the idea to add this visual.

I also found documentation for a soluton using R (with which I'm personally not familiar).

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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