Comparing 2 columns

[kalyan209]

HI,

I am comparing 2 columns and displaying the required text in the new column using the specs below.

• IF Doc Type = Observation THEN “Audit Report without Observation”
• IF Doc Type = Audit AND TRN ID is in  Parent ID Field THEN “Audit Report with Observation”
• IF Doc Type =Audit AND  TRN is not in Parent ID Field THEN “Audit Report without Observation”

From the image below i need to check if 148833 is present in PARENT_ID and display "Audits with observation". 

The formula i used is: 

Counts = IF('Audit and Obs Data'[DOC_TYPE] = "Observation", "Audit without Observation", IF('Audit and Obs Data'[DOC_TYPE]="Audit" && CONTAINS('Audit and Obs Data','Audit and Obs Data'[TRN_ID],'Audit and Obs Data'[PARENT_ID]), "Audit","Audits without Observation"))

thanks in advance

[v-frfei-msft · Accepted solution]

Hi @kalyan209 ,

We can create a calculated column as below.

Column =
VAR par =
    VALUES ( 'Audit and Obs Data'[PARENT_ID] )
RETURN
    IF (
        'Audit and Obs Data'[DOC_TYPE] = "Observation",
        "Audit Report without Observation",
        IF (
            'Audit and Obs Data'[DOC_TYPE] = "Audit"
                && 'Audit and Obs Data'[TRN_ID] IN par,
            "Audit Report with Observation",
            IF (
                'Audit and Obs Data'[DOC_TYPE] = "Audit"
                    && NOT ( 'Audit and Obs Data'[TRN_ID] IN par ),
                "Audit Report without Observation"
            )
        )
    )

For more details, please check the pbix as attached.