Forum Discussion

Hash2023's avatar
Hash2023
Frequent Visitor
2 years ago

Combine data from different tables with a filter

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

IDName
1010Max
1020Tom
1030Harry

 

Table: Transaction_Jour

IDExtra_PurchaseExtra_Purchase_DateExtra Code
1010Rs 200005/01/20238
1010Rs 170003/01/20238
1020Rs 130008/01/20235
1030Rs 110004/01/20234
1030Rs 200005/01/20232
1030Rs 130003/01/20233

 

Table: Transaction_Code

Extra CodeRevenue_Code
8Extra Revenue
8Other Revenue
5Extra Revenue
4Other Revenue
2Extra Revenue
3Other Revenue

 

Final Table should look like

IDNameExtra_ Purchase_Date
1010Max 03/01/2023
1020Tom 08/01/2023
1030Harry 05/01/2023

 

Is there a way i could do this Power BI?

 

Many Thanks.

 

Kind Regards,

General Comment
Need Help
Show and Tell
Tips and Tricks

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Hash2023
      Frequent Visitor
      2 years ago

      Hi Anonymous ,

       

      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,