Help on retrieving values from other tables based on lookup and if

[Anonymous]

Hello community,

PowerBI newbie here and I'll appreciate any help I can get.

Here's the data that I'm working with:

Contract
CUSTOMER_ID	CONTRACT_NUMBER	PRODUCT_TYPE	CONTRACT RATE (F)	CONTRACT RATE (D)
10002	C000148691	F	17.27
10004	C000148593	D		2%
10273	C000148681	F	24.5
14000	C000148727	D		3.10%
16000	C000148685	D		5%

Usage
CONTRACT_NUMBER	USAGE_START_DATE	USAGE_END_DATE	USAGE	PRODUCT_TYPE	ALT_RATE	FINAL_RATE
C000148691	1/1/2024	2/1/2024	78	F	23	?
C000148593	1/27/2024	2/27/2024	90	D	23.5
C000148681	1/27/2024	2/27/2024	24	F	26.5
C000148727	1/1/2024	2/1/2024	54	D	17
C000148685	3/22/2024	4/22/2024	39	D	27.8

So essentially there are 2 parts to what I want to do here. I need to do a lookup as well as an 'if' statement which will result in varying outputs.
I need to add a FINAL_RATE calculated column to my Usage table. And the value to be populated in here will be dependent on the product type.

Case 1: Product Type = F
Match by CONTRACT_NUMBER in Contract and Usage tables
Usage'[FINAL_RATE] ='Contract'[CONTRACT RATE (F)]

Case 2: Product Type = D
Match by CONTRACT_NUMBER in Contract and Usage tables
Usage'[FINAL_RATE] = (1-'Contract'[CONTRACT RATE(D)]) * 'Usage'[ALT_RATE]

Thank you!!

[rajendraongole1 · Accepted solution]

 Hi @Anonymous - Create a new column in your usage table as below:

FINAL_RATE =
IF(
'Usage'[PRODUCT_TYPE] = "F",
LOOKUPVALUE('Contract'[CONTRACT RATE (F)], 'Contract'[CONTRACT_NUMBER], 'Usage'[CONTRACT_NUMBER]),
IF(
'Usage'[PRODUCT_TYPE] = "D",
(1 - LOOKUPVALUE('Contract'[CONTRACT RATE (D)], 'Contract'[CONTRACT_NUMBER], 'Usage'[CONTRACT_NUMBER])) * 'Usage'[ALT_RATE],
BLANK()
)
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!