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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors