Conversion of sql if statement to DAX

[jak82]

Help!

I need to convert the following into a dax calculated column ,

CASE 
                  WHEN  t3.specY > 0 THEN t3.specY 
                  ELSE t3.calcY 
         END * 
         CASE 
                  WHEN  t3.specX > 0 THEN t3.specX 
                  ELSE t3.calcX 
         END  as XandYProduct, 
         CASE 
                  WHEN t3.specX > 0 THEN t3.specX 
                  ELSE t3.calcX 
         END as SpecXOrCalcX, 
CASE 
                  WHEN 
                           CASE 
                                    WHEN  t3.specY > 0 THEN t3.specY 
                                    ELSE t3.calcY 
                           END * 
                           CASE 
                                    WHEN  t3.specX > 0 THEN t3.specX 
                                    ELSE t3.calcX 
                           END > 0 THEN 
                           CASE 
                                    WHEN  t3.chargfl = 'Y' THEN t7.cpi 
                                    ELSE 0 
                           END / ( 
                           CASE 
                                    WHEN  t3.specY > 0 THEN t3.specY 
                                    ELSE t3.calcY 
                           END * 
                           CASE 
                                    WHEN  
                                                      t3.specX > 0 THEN t3.specX 
                                    ELSE t3.calcX 
                           END  
                  ELSE  0
         END

Can anyone help!

Thanks

Chris

 

[Matt Allington]

I suggest you use Power Query conditional columns on data load. Read about it at my blog here Conditional Columns in Power BI Desktop - Excelerator BI

I'm pretty sure it is available in Power Query

 

[jak82]

Thanks Matt, didn't know about that feature will check it out

 

[jak82]

Hi Matt,

The conditional column option does not seem to exist on my 'Add Column' Tab. I am running Excel 2016 Pro.

 

[Matt Allington]

Oh. Sorry about that. Could you use this method? Banding in DAX - Excelerator BI