topic Re: How do I create a new table based off another column's rows? in DAX Commands and Tips

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

bballjoe12 — Thu, 10 Feb 2022 01:35:22 GMT

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.

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

amitchandak — Thu, 10 Feb 2022 03:59:49 GMT

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/

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

bballjoe12 — Thu, 10 Feb 2022 04:53:35 GMT

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.'

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

v-chenwuz-msft — Mon, 14 Feb 2022 07:27:51 GMT

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.