Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gmasta1129
Helper III
Helper III

Comparing two columns in two different tables (Greater Than)

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

1 REPLY 1
ChrisMendoza
Resident Rockstar
Resident Rockstar

@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:

image.png






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.