2 columns contain x, then y - Custom column

[Ozcoz]

Hi there,

I've looked in the forums and can't find a solution to my simple issue.

The table has 2 columns 'Season' and 'Endorsed_season' and I'm looking for discrepancies in the two fields. 

Return 'Valid' if both columns contain the word 'spring', or they both contain 'autumn'.

Otherwise, return 'Invalid'.

In the above image, the result should be: Invalid for row 1, then Valid for row 2.

The following code returns an error:

Season_valid =
IF(
(CONTAINS(all_burn[Season], "Spring") & CONTAINS(all_burn[Endorsed_Season], "Spring")),
"Valid",
IF(
(CONTAINS(all_burn[Season], "Autumn") & CONTAINS(all_burn[Endorsed_Season], "Autumn")),
"Valid",
"Invalid"
)
)

I also tried creating a column from examples, but it wasn't smart enough to work it out the pattern.

Thanks.

[v-junyant-msft · Accepted solution]

Hi @Ozcoz ,

You can use the following DAX to create a new column:

Valid_Invalid = 
IF(
    CONTAINSSTRING('Table'[Season], "SPRING") && CONTAINSSTRING('Table'[Endorsed_season], "SPRING") ||
    CONTAINSSTRING('Table'[Season], "AUTUMN") && CONTAINSSTRING('Table'[Endorsed_season], "AUTUMN"),
    "Valid",
    "Invalid"
)

And the final output is shown in the following figure:

Best Regards,

Dino Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM]

Hey 

Please try these 2:
Custom column in PQ:
= Table.AddColumn(#"Changed Type", "Custom", each if (Text.Contains([SEASON], "Spring") or Text.Contains([SEASON], "Autumn"))
and (Text.Contains([ENDORSED_SEASON], "Spring") or Text.Contains([ENDORSED_SEASON], "Autumn"))
then "Valid"
else "Invalid")

and this one as the custom column 1:
= Table.AddColumn(#"Added Custom", "Custom.1", each if (Text.Contains([SEASON], "Spring") and Text.Contains([ENDORSED_SEASON], "Spring")) or (Text.Contains([SEASON], "Autumn") and Text.Contains([ENDORSED_SEASON], "Autumn"))
then "Valid"
else "Invalid")

the output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[Ozcoz]

Thanks for your reply.

Can you clarify, are these 2 different ways to reutrn the result, or are they both required?

I created a custom for both of these options in PQ editor, but they both show table.

When I click on table, they both return a column of Invalid only. 

I'm not super adept with PowerBI, am I doing something wrong?

[VahidDM]

Sorry for that 🙂
Use this as custom column:
= Table.AddColumn(Source, "Custom", each if ((Text.Contains([SEASON], "Spring") and Text.Contains([ENDORSED_SEASON], "Spring")) or (Text.Contains([SEASON], "Autumn") and Text.Contains([ENDORSED_SEASON], "Autumn")))
then "Valid"
else "Invalid")

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[Ozcoz]

Sorry, but it still returns 'Table'.

Clicking on it returns an error on all rows and it removes the headers, essentially making each column Column 1, Column 2 as headings.

Clicking on the error says:

Expression.Error: The field 'SEASON' of the record wasn't found. But this is likely due to the lack of headings.