March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
Solved! Go to 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.
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.
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
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?
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!!
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
90 | |
86 | |
76 | |
49 |
User | Count |
---|---|
166 | |
149 | |
99 | |
73 | |
57 |