How do I create a new table based off another column's rows?

[bballjoe12]

I have a table (the table name is "Tickets") that has 53,000 ticket #s.  On this table, the ticket #s are all unique.  I have another table (the table name is "Ticket History") that includes ticket #s and multiple rows of that ticket # that document all the history of the ticket (change of team or change of assignee, etc.).  The data may consist of 4 rows for, example, for ticket # 126748, because it was assigned to 4 different teams. This ticket history table is extremely large, well over 2 million rows.

What I am trying to do is create a third table that if the "Ticket History" table contains any of the ticket #s from the "Tickets" table, to show all of the rows where that ticket # displays.  Here is an example:

How would I go about doing this using DAX to create a third table? The "Tickets" and "Ticket History" tables do not have have the same amount of columns, if that makes a difference and there is no relationship between the tables.

[v-chenwuz-msft · Accepted solution]

Hi @bballjoe12 ,

You mean remove all rows from Tickets History table where thicket numbers have not appeared in Ticket Number table?

This dax code:

Combined Tables = FILTER('Ticket History',[Ticket Numbers] in VALUES(Tickets[Ticket Numbers]))

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@bballjoe12 , In power query you can use merge.

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

and in Dax you can try natural inner join

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

[bballjoe12]

I tried the innerjoin route and got this error message:

'No common join columns detected. The join function 'NATURALLEFTOUTERJOIN' requires at-least one common join column.'