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  "Wand...
Need Help
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