Forum Discussion

Phil-osophy's avatar
Phil-osophy
Helper I
7 years ago
Solved

Matching transactions across multiple tables

Hi  everyone,   ive come across an issue in my dataset that i need help solving. i have 4 different tables: -travel extract -intermediary file -traveler ID number -traveller card expenses file ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    Enter this formula there

    =if(ISNUMBER(CALCULATE(COUNTROWS(travel),FILTER(travel,travel[trip date from]<=EARLIER(card_expenses[Transaction Date])&&travel[trip date to]>=EARLIER(card_expenses[Transaction Date])&&travel[Traveller ID]=EARLIER(card_expenses[Employee id])))),"Travel status","Not in travel status")
Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

Here's the result i got without building any relationships.  Download the Excel file from here.

Hope this helps.

  • Phil-osophy's avatar
    Phil-osophy
    Helper I
    7 years ago

    Thank you Ashish_Mathur !

     

    Would you know how to create a formula that would work with the relationships seeing as that is what i have in my actual power BI file because my dataset is significantly larger than what i shared?

     

    thanks!

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      7 years ago

      Hi,

      My formula should work even if you build relationships between the tables.

  • Phil-osophy's avatar
    Phil-osophy
    Helper I
    7 years ago

    Hi Ashish_Mathur,

     

    i dont believe that is the correct excel file link as i dont see any formula included. it seems to be the same excel files i shared.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      7 years ago

      Hi,

      Go to PowerPivot > Manage.

      • Phil-osophy's avatar
        Phil-osophy
        Helper I
        7 years ago

        thank you!

         

        do you know if there is any way to output something similar to what i had in my formula (if the expense took place during travel dates, the output is '' travel status'', otherwise if it took place outside of travel dates the output is ''not in travel status'')?