DAX for finding matches in multiple columns

[_Kelz0484]

Hi,

Hope I'm posting this in the right place.

I'm trying to write a DAX command to find if a column that contains a certificate number matches two other columns within the same table.

Column 1 Certificate number (made up of a serial number or batch number plus a date) 

Column 2 Serial number

Column 3 Batch number

Column 4 Date

What I'm trying to do is check Column 1 against 2, 3 & 4 and if it finds a match of either 2 or 3 plus 4 mark as "Match" or if not then "Discrepancy"

Any help would be appreciated!

Thanks.

[Anonymous]

Hi @_Kelz0484 ,

Thanks johnt75  for the quick reply and solution. I have some other ideas to add:

(1) This is my test data.

(2) Create a calculated column.

MatchStatus = 
IF (
    CONTAINSSTRING ( [Certificate Number], [Serial Number] & [Date] ) 
    || CONTAINSSTRING ( [Certificate Number], [Batch Number] & [Date] ),
    "Match",
    "Discrepancy"
)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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

[_Kelz0484]

Hi, thanks for your reply. This would have been a perfect solution had the date format been the same in both the date column and the certificate no. column. Because my data source is so large it would be an arduous task to change the format to suit and there is no option in power bi date format drop down that would transpose to DD-MMM-YYYY as that is how it is typed in the certificate no.

Your reply is much appreciated though! Thank you.

[johnt75 · Accepted solution]

You could create a calculated column like

Match =
VAR DateToCheck =
    RIGHT ( 'Table'[Column1], 10 )
VAR NumberToCheck =
    LEFT ( 'Table'[Column1], 20 ) -- Replace the logic here as needed
VAR AllSerialNos =
    CALCULATETABLE ( ALL ( 'Table'[Column2] ), 'Table'[Column4] = DateToCheck )
VAR AllBatchNos =
    CALCULATETABLE ( ALL ( 'Table'[Column3] ), 'Table'[Column4] = DateToCheck )
VAR Result =
    IF (
        NumberToCheck
            IN AllSerialNos
                || NumberToCheck IN AllBatchNos,
        "Match",
        "Discrepancy"
    )
RETURN
    Result

You'll need to change the logic splitting out the number from the date appropriately, and you may need to manipulate the date data to get it in date format to match Column4

[_Kelz0484]

Thank you. This worked perfect!