Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
rasalaprashanth
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 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

 

Lookup Table

ApproachAgeLTD%M&R
Poor0115175
Marginal2585125
Adequate506575
Good754525
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 NUMBEREquipment AgeLTD USAGEM&R % LifeAge_Life_s
R0732015.6         182,996295%-29.675.00
R0521419.5         103,401218%-62.265.00
R0631817.8         101,181200%-48.715.00
R0631617.8            98,367122%-48.555.00
1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

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:

vtangjiemsft_0-1675933387235.png

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. 

View solution in original post

2 REPLIES 2
v-tangjie-msft
Community Support
Community Support

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:

vtangjiemsft_0-1675933387235.png

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
Solution Specialist
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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.