Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
@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:
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |