Comparing two columns in two different tables (Greater Than)

[gmasta1129]

Hello,

I have three columns in one table:

Run Date

Facility Code

Margin Account Balance

and I have three columns in a second table:

Run Date

Facility Code

FX PRV

I want to compare the Margin Account Balance to the FX PRV by Facility Code and run date.  If the value in the Margin Account Balance is greater than the value in FX PRV for a specific facility code and run date, then it should pull in 1, if not then pull in 2.  

I went to modeling then "Manage relationships" and connected both files together by Facility Code and Run Date.  

Please see example below

Table 1

Run Date            Facility Code        Margin Account Balance

4/5/2023               11111                     5

 4/5/2023              22222                     6

 4/5/2023              33333                     1

Table 2 

Run Date            Facility Code       FX PRV

 4/5/2023            11111                     3

4/5/2023               22222                   2

4/5/2023                33333                   4

The new table I want to create: 

Please note, the Slicer (filter) will be the "Run Date" column. 

Slicer "Run Date" = 4/5/2023

Facility Code      Margin Account Balance      FX PRV       New Column (requirements mentioned above)

11111                           5                                     3                        1

22222                           6                                     2                        1

33333                           1                                     4                        2

[ChrisMendoza]

@gmasta1129 - create a new table as:

New Table = 
 ADDCOLUMNS(
     Table1,
     "@FX PRV",
     LOOKUPVALUE(Table2[FX PRV],Table2[Facility Code],Table1[Facility Code])
 )

add new column as:

Column (requirements mentioned above) = 
IF(
    'New Table'[Margin Account Balance] > 'New Table'[@FX PRV],
    1,
    2
)

result: