Forum Discussion

RodrigoSV's avatar
RodrigoSV
Regular Visitor
3 years ago
Solved

Excel Formula to DAX

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!

  • BrianConnelly's avatar
    BrianConnelly
    3 years ago

    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")
        )

1 Reply

  • BrianConnelly's avatar
    BrianConnelly
    Icon for Resolver III rankResolver III
    3 years ago

    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")
        )