cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lynnsop
Helper I
Helper I

New table from two tables with condition

Hey Guys.

 

I have 3 tables.

1 Base table ("a")

2 Data Tables ("b","c")

All of them have a username column.

 

Now I need 2 new tables with conditional rows:

(1) First new table: Includes only rows where username from "b" is also in "a".

(2) Second new table: Includes only rows where username from "c" is not in "a". (basically c-a)

 

How can I realize that? For (1) I tried merging queries in query editor but this seems to be buggy?

It shows that 164 rows (usernames) are matching but when I create the table it seems to be done nothing:

lynnsop_0-1626764185950.png

Resulting table: 

lynnsop_1-1626764193825.png

I'm also open to any DAX Logic that creates new tables.

In result I would need two new seperate tables.

 

Thanks in advance 🙂

 

 

1 ACCEPTED SOLUTION
selimovd
Super User
Super User

Hey @lynnsop ,

 

I would do the work all in Power Query. The joins that you want are already available and you won't have additional tables in the data model that you don't need.

Please find my example file here:

https://www.swisstransfer.com/d/9387d28f-e05e-436e-9151-3de28ad519fd

 

For your first case, you only have to do an inner join:

selimovd_0-1626801191363.png

 

For the second case you have to do a "left anti":

selimovd_1-1626801209304.png

 

And that should already give you the result you want.

 

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
 

 

 

 

View solution in original post

3 REPLIES 3
selimovd
Super User
Super User

Hey @lynnsop ,

 

I would do the work all in Power Query. The joins that you want are already available and you won't have additional tables in the data model that you don't need.

Please find my example file here:

https://www.swisstransfer.com/d/9387d28f-e05e-436e-9151-3de28ad519fd

 

For your first case, you only have to do an inner join:

selimovd_0-1626801191363.png

 

For the second case you have to do a "left anti":

selimovd_1-1626801209304.png

 

And that should already give you the result you want.

 

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
 

 

 

 

View solution in original post

amitchandak
Super User
Super User

@lynnsop , Try two new tables

 

new table =
var _tab = INTERSECT(TableB[username], TableA[username])
return
calculatetable(filter(TableA, TableA[user] in _tab))

 

new table 2 =
var _tab = except(TableC[username], TableA[username])
return
calculatetable(filter(TableC, TableC[user] in _tab))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Hi @amitchandak 

the "intersect" and "except" both seem to require a single table and not column of table. Therefore I get an error when I want to implement this solution

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

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!