Forum Discussion

_Kelz0484's avatar
_Kelz0484
Frequent Visitor
1 year ago
Solved

DAX for finding matches in multiple columns

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.

  • johnt75's avatar
    johnt75
    1 year ago

    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

4 Replies

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      _Kelz0484
      Frequent Visitor
      1 year ago

      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.