DAX with Lookup

[rasalaprashanth]

Hello All,

I have to replicate an Excel formula in POWER BI 

I have 2 tables 

Unit_Table

UNIT NUMBER	Equipment Age	LTD USAGE	M&R %
R07320	15.6	182,996	295%
R05214	19.5	103,401	218%
R06318	17.8	101,181	200%
R06316	17.8	98,367	122%

Lookup table

Modeling Parameters:	Age	LTD	%M&R
(See Input Parameter Spreadsheet)
Weights	0.50	0.2	0.3
Proposed Replacement Cycle	12.00
Utilization Threshold		120000

the Excel Formula i need to replicate is life =(1-Equipment Age(unit_table)/{Lookup table-> Proposed replacement cycle-->age})*100

for Unit Number life should be (1-15.6/12.00)*100 = -29.67

UNIT NUMBER	Equipment Age	LTD USAGE	M&R %	life
R07320	15.6	182,996	295%	-29.67
R05214	19.5	103,401	218%	-62.26
R06318	17.8	101,181	200%	-48.71
R06316	17.8	98,367	122%	-48.55

Thanks

[v-cgao-msft · Accepted solution]

Hi @rasalaprashanth ,

Please try this column:

life = 
VAR _age = CALCULATE(MAX('Lookup table'[Age]),FILTER(ALL('Lookup table'),'Lookup table'[Modeling Parameters:]="Proposed Replacement Cycle"))
VAR _result = (1-DIVIDE('Unit_Table'[Equipment Age],_age))*100
RETURN
_result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum