cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## 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=

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
1 ACCEPTED SOLUTION
Community Support

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 _Good=MAXX( FILTER('Look Table','Look Table'[Approach]="Good") , [Age])

return

SWITCH(TRUE(),

[ Life]<= _Poor,5,

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

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 2
Community Support

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 _Good=MAXX( FILTER('Look Table','Look Table'[Approach]="Good") , [Age])

return

SWITCH(TRUE(),

[ Life]<= _Poor,5,

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

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.

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.