DAX IF condition with Lookup

[rasalaprashanth]

Hello All, 

I have a requirement to convert the Excel formula to power bi dax 

Unit_table

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

Lookup Table

Approach	Age	LTD	%M&R
Poor	0	115	175
Marginal	25	85	125
Adequate	50	65	75
Good	75	45	25
Excellent

Excel Formula for Age_Life_s= 

=IF(life (Unit_table)< Lookup_table-->Poor-->age,5,IF(AND(life (Unit_table)>Lookup_table-->Poor-->age,life (Unit_table)<=Lookup_table-->Marginal-->age),4,IF(AND(life (Unit_table)>Lookup_table-->Marginal-->age,life (Unit_table)<= Lookup_table-->adequate-->age),3,IF(AND(life (Unit_table)>Lookup_table-->adequate-->age),life (Unit_table)<=Lookup_table-->good-->age),2,1))))

Out put table 

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

[Anonymous · Accepted solution]

Hi @rasalaprashanth ,

According to your description, you want to convert the excel formula to dax. In your code , it seems to get the flag data by the ‘Look Table’[Age].

If this , here are the steps you can refer to :
(1)My test data is the same as yours.

(2)We can click “New Column” and enter this :

Column = var _Poor=MAXX( FILTER('Look Table','Look Table'[Approach]="Poor") , [Age])

var _Marginal=MAXX( FILTER('Look Table','Look Table'[Approach]="Marginal") , [Age])

var _Adequate=MAXX( FILTER('Look Table','Look Table'[Approach]="Adequate") , [Age])

var _Good=MAXX( FILTER('Look Table','Look Table'[Approach]="Good") , [Age])

return

SWITCH(TRUE(),

[ Life]<= _Poor,5,

[ Life]>_Poor &&[ Life]<=_Marginal,4,

[ Life]>_Marginal &&[ Life]<=_Adequate,3,

[ Life]>_Adequate &&[ Life]<=_Good,2,

1)

(3)Then we can meet your need ,the result is as follows:

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[JirkaZ]

My head is spinning from so many IFs in one statement. 

Try to use the SWITCH(TRUE(), ....) statement to implement the logic. 
For the look-ups you can use LOOKUPVALUE function.