Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Nested IF

Can anyone help me with the logic to create the below output: 

IF "Code" = S, then in the  "Wanding CE210" Output also = "S" & in the next line should be a blank
IF "Code" = P, then in the  "Wanding CE210" Output also = "P" & in the next line should be a blank
Thanks in advance.


The codes which have been created so far has been pasted below.

Wanding CE210 = 
var a = MAX('CE210'[Index1])
var b = CALCULATE (MAX('CE210'[ITDSC]) ,FILTER ( ALLEXCEPT('CE210',CE210[WKCTR],CE210[DATE]), 'CE210'[Index1] = a  - 1 ) )
var c = CALCULATE (MAX ( 'CE210'[SIZE] ),FILTER ( ALLEXCEPT('CE210',CE210[WKCTR],CE210[DATE]), 'CE210'[Index1] = a  - 1  ) )
var d = CALCULATE (MAX ( 'CE210'[HEIGHT] ),FILTER ( ALLEXCEPT('CE210',CE210[WKCTR],CE210[DATE]), 'CE210'[Index1] = a  - 1  ) )
var e = 
IF(MAX('CE210'[WKCTR]) = BLANK(),BLANK(),
IF(
    MAX('CE210'[LABORCODE]) = "S",
    "S" ,
    IF(
    MAX('CE210'[LABORCODE]) = "P",
    "P",
    IF(
    MAX('CE210'[LABORCODE]) = "C",
    "C",
    IF (SEARCH("598",MAX('CE210'[RTOOL]),,0)=0 && MAX('CE210'[HEIGHT]) <>d, "D",
        IF (
        MAX('CE210'[ITDSC]) = b,
            IF (
            MAX('CE210'[SIZE]) = c,
            "M",
            "A"
        ),
        "B"
    ))))))
return
e
Index1 = RANKX(FILTER('CE210','CE210'[WKCTR] = EARLIER(CE210[WKCTR]) && 'CE210'[DATE] = EARLIER('CE210'[DATE])),'CE210'[Start_Time],,ASC,Dense)
Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous  - It looks like you're already retrieving certain information from the previous row and loading them into variables, such as:

    var b = CALCULATE (MAX('CE210'[ITDSC]) ,FILTER ( ALLEXCEPT('CE210',CE210[WKCTR],CE210[DATE]), 'CE210'[Index1] = a  - 1 ) )

    You can load the CODE and Wanding CE210 from the previous row into variables:

    var previous_code = CALCULATE (MAX('CE210'[CODE]) ,FILTER ( ALLEXCEPT('CE210',CE210[WKCTR],CE210[DATE]), 'CE210'[Index1] = a  - 1 ) )
    var previous_wanding_ce210 = CALCULATE (MAX('CE210'[Wanding CE210]) ,FILTER ( ALLEXCEPT('CE210',CE210[WKCTR],CE210[DATE]), 'CE210'[Index1] = a - 1 ) )

    Then, you can use IF like this:

    IF(
    OR(
        AND(previous_code = "P", previous_wanding_ce210 = "P"),
        AND(previous_code = "S", previous_wanding_ce210 = "S")
        ), 
        BLANK(),
        <Other Logic....>
    )

    Hope this helps,

    Nathan