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

create a join table from 2 tables both same fields and same filter

Hi everyone,
I'm trying to creat a dax table that can join two tables filtering the ICM column <> "Normal" in both tables.
Thank you so much
Pedro

1 ACCEPTED SOLUTION

@Anonymous 

Something like

 

union (
summarize(filter(Table1,Table1[column5]= "Normal"),Table1[column1],Table1[column2],Table1[column3],Table1[column4]),
summarize(filter(Table2,Table2[column5]= "Normal"),Table2[column1],Table2[column2],Table2[column3],Table2[column4])
summarize(filter(Table3,Table3[column5]= "Normal"),Table3[column1],Table3[column2],Table3[column3],Table3[column4])
)

 

 

Also refer

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

Anonymous
Not applicable

@amitchandak 
Sure i can.
Instead of duplicating a couple of queries, and filter both by a certain column = "normal" and then append them, is it possible that, through dax i can make that table?
Thanks

Few ways

Selectcolumns

 

new Table = selectcolumns(table,"Col1", table[Col1], "COl2", Table[Col2])

Basically like a view now grouping , rename columns

new table =summarize(table,table[Col1], "COl2",sum( Table[Col2]))

Group by do not rename , Aggergated column have to rename

 

Table1 =Table //referance table

Table1 = distinct(Table) //new Table

Table1 = distinct(Table[Column]) //Distinct of column

 

There are a few more

 

Anonymous
Not applicable

@amitchandak Thank you!
I'm sorry to insist but (although i don't understand dax) i don't see in the instructions the join or the append between those tables.

Let's suppose i have table1, table2 and table3. All 3 tables, i just want all records with column1, column2, column3 and column4, regarding column5 = "Normal". These column5 exists in every table

I thank you very much for your patience

@Anonymous 

Something like

 

union (
summarize(filter(Table1,Table1[column5]= "Normal"),Table1[column1],Table1[column2],Table1[column3],Table1[column4]),
summarize(filter(Table2,Table2[column5]= "Normal"),Table2[column1],Table2[column2],Table2[column3],Table2[column4])
summarize(filter(Table3,Table3[column5]= "Normal"),Table3[column1],Table3[column2],Table3[column3],Table3[column4])
)

 

 

Also refer

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Anonymous
Not applicable

@amitchandak you're the boss
Such a simple instruction!
Awesome. Thank you so much.

for append

union (
summarize(Table,Table[Col1],"Measure1",[Measure1]),
summarize(Table2,Table2[Col1],"Measure2",[Measure2])
summarize(Table3,Table3[Col1],"Measure3",[Measure3])
summarize(Table4,Table4[Col1],"Measure4",[Measure4])
)

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.