Excel Formula to DAX

RodrigoSV — Mon, 20 Feb 2023 17:52:31 GMT

Hello All,

I have an Excel formula that needs to be converted to DAX:

'=IF(AND([@[CREATION DATE < 6 MONTHS]]="";[@[PLANT_LEVEL_STATUS]]="ACTIVE";[@[IS THE LAST INVOICE DATE < 730 DAYS?]]="";[@STOCK]="";[@[DOES IT HAVE OPEN SALES ORDERS?]]="";[@[DOES IT HAVE OPEN PRODUCTION ORDERS?]]="";[@[DOES IT HAVE ACTIVE PURCHASE ORDERS?]]="");"INACT PLANT"; IF(AND([@[CREATION DATE < 6 MONTHS]]="";[@[PLANT_LEVEL_STATUS]]="ACTIVE";[@[IS THE LAST INVOICE DATE < 730 DAYS?]]="YES");"NO ACTION"; IF(AND([@[CREATION DATE < 6 MONTHS]]="< 6 months";[@[PLANT_LEVEL_STATUS]]="INACTIVE");"POSSIBLE REACTIVATION PLANT"; IF(AND([@[CREATION DATE < 6 MONTHS]]="";[@[PLANT_LEVEL_STATUS]]="ACTIVE";[@[IS THE LAST INVOICE DATE < 730 DAYS?]]="";[@STOCK]<>"";[@[DOES IT HAVE OPEN SALES ORDERS?]]="";[@[DOES IT HAVE OPEN PRODUCTION ORDERS?]]="";[@[DOES IT HAVE ACTIVE PURCHASE ORDERS?]]="");"PHASE OUT PLANT"; ))))' AS 'INACT/PHASE OUT',

I have tried to start with a column like this:

Plant Status = IF( AND( [CREATION DATE < 6 MONTHS] = BLANK(), [PLANT_LEVEL_STATUS] = "ACTIVE", [IS THE LAST INVOICE DATE < 730 DAYS?] = BLANK(), [STOCK] = BLANK(), [DOES IT HAVE OPEN SALES ORDERS?] = BLANK(), [DOES IT HAVE OPEN PRODUCTION ORDERS?] = BLANK(), [DOES IT HAVE ACTIVE PURCHASE ORDERS?] = BLANK() ), "INACT PLANT", IF( AND( [CREATION DATE < 6 MONTHS] = BLANK(), [PLANT_LEVEL_STATUS] = "ACTIVE", [IS THE LAST INVOICE DATE < 730 DAYS?] = "YES" ), "NO ACTION",

All the conditions are already created in PBI, however, it seems that my DAX query isn't correct. How should I proceed to replicate it?

Thank you very much!

Re: Excel Formula to DAX

BrianConnelly — Mon, 20 Feb 2023 18:22:49 GMT

First, the AND function in DAX accepts only two (2) arguments. You can't write that in that manner. Change your code to something like this:

AND( [CREATION DATE < 6 MONTHS] = BLANK(), AND([PLANT_LEVEL_STATUS] = "ACTIVE", AND([IS THE LAST INVOICE DATE < 730 DAYS?] = BLANK(), AND([STOCK] = BLANK(), AND([DOES IT HAVE OPEN SALES ORDERS?] = BLANK(), AND([DOES IT HAVE OPEN PRODUCTION ORDERS?] = BLANK(),[DOES IT HAVE ACTIVE PURCHASE ORDERS?] = BLANK()))))) )

and the second part as:

AND( [CREATION DATE < 6 MONTHS] = BLANK(), AND([PLANT_LEVEL_STATUS] = "ACTIVE", [IS THE LAST INVOICE DATE < 730 DAYS?] = "YES") )

topic Re: Excel Formula to DAX in DAX Commands and Tips

Excel Formula to DAX

Re: Excel Formula to DAX