Combine data from different tables with a filter

[Hash2023]

Hello Team,

I have 3 tables (ID, Transaction_Jour and Transaction_Code). I want to bring column "Extra_Purchase_Date" in Transaction_Jour to the table ID with the rules that it brings the first transaction based on the the first date and Revenue_Code "Extra Revenue" which is in the table Transaction_Code.

Example:

Table: ID

ID	Name
1010	Max
1020	Tom
1030	Harry

Table: Transaction_Jour

ID	Extra_Purchase	Extra_Purchase_Date	Extra Code
1010	Rs 2000	05/01/2023	8
1010	Rs 1700	03/01/2023	8
1020	Rs 1300	08/01/2023	5
1030	Rs 1100	04/01/2023	4
1030	Rs 2000	05/01/2023	2
1030	Rs 1300	03/01/2023	3

Table: Transaction_Code

Extra Code	Revenue_Code
8	Extra Revenue
8	Other Revenue
5	Extra Revenue
4	Other Revenue
2	Extra Revenue
3	Other Revenue

Final Table should look like

ID	Name	Extra_ Purchase_Date
1010	Max	03/01/2023
1020	Tom	08/01/2023
1030	Harry	05/01/2023

Is there a way i could do this Power BI?

Many Thanks.

Kind Regards,

[v-xiandat-msft]

Hi @Hash2023 ，

Below is my table1:

Below is my table2:

Below is my table3:

The following DAX might work for you:

Extra_Purchase_Date = 
   var pre_date = MIN(Tracsaction_Jour[Extra_Purchase_Date])
   return pre_date

The final output is shown in the following figure:

Best Regards,

Xianda Tang

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

[Hash2023]

Hi @v-xiandat-msft ,

Thank you for your response and help. I appreciate your help on this matter.

The proposed idea unfortunately, does not seem to be working in my case. 😞

I pulled the Revenue Code from table 3 into table 1.

I have been trying and the statement below seems to be working:

CALCULATE(FIRSTNONBLANK(TransactionJournal[purchase_date],1),FILTER((TransactionJournal),'Id'[hotelregno_id]=TransactionJournal[hotelregno_id]))

However, I need to add the condition that: TransactionJournal[Extra_Type] = "Extras Revenue" which i haven't been able to so far. Any idea on how i can do it?

Many Thanks.

Kind Regards,