Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX with Lookup

Hello All,

I have to replicate an Excel formula in POWER BI 

I have 2 tables 

Unit_Table

UNIT NUMBEREquipment AgeLTD USAGEM&R %
R0732015.6         182,996295%
R0521419.5         103,401218%
R0631817.8         101,181200%
R0631617.8            98,367122%

 

Lookup table

Modeling Parameters:AgeLTD%M&R
(See Input Parameter Spreadsheet)   
Weights0.500.20.3
Proposed Replacement Cycle12.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 NUMBEREquipment AgeLTD USAGEM&R %life
R0732015.6         182,996295%-29.67
R0521419.5         103,401218%-62.26
R0631817.8         101,181200%-48.71
R0631617.8            98,367122%-48.55

 

Thanks

Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    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