Forum Discussion
DAX IF condition with Lookup
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 |
Hi Anonymous ,
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.
2 Replies
- JirkaZ
Solution Specialist
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. Hi Anonymous ,
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.
