Compare the value multiple column

[Anonymous]

trying to compare the multiple columns to find  item available and item not avaialbe 

Itemcode       Reference Bom           Itemcode Bom           Description 

 1011                    FZ                                  FZ                     TearTap    

 1022                   GH                                  GH                    Printed

 1033                    KL                                                           item not required - BOM 

 1044                    VB                                                           Bundlepaer 

 1055                    CB                                   CB                    Case lable 

i am trying to compare if both  reference  bom and Itemcode bom  values are same  its return  Item Available .

If  refernce bom and itemcode bom  values are not match  its return  Item not available .'

another condition if refence bom and itemcode bom values  not match or blank  same time description column if word contain  item not required its return No issues.

Expected output :

Itemcode       Reference Bom           Itemcode Bom           Description                                   Status 

 1011                    FZ                                  FZ                     TearTap                                         Item available 

 1022                   GH                                  GH                    Printed                                         Item available 

 1033                    KL                                                           item not required - BOM             No issues 

 1044                    VB                                                           Bundlepaer                                  Item Not available    

 1055                    CB                                   CB                    Case lable                                   Item available  .

Looking for support .thanks in advance . 

[DimaMD · Accepted solution]

hi @Anonymous try it colum

Status = 
 SWITCH(
     TRUE(),
     [Reference Bom] = [Itemcode Bom], "Item available",
     AND( [Itemcode Bom] = BLANK(), [Description] = "item not required - BOM"), "No issues",
     AND( [Reference Bom] <> BLANK(), [Itemcode Bom] = BLANK()), "Item Not available")

[Anonymous]

@DimaMD  Thank you so much its working 

[AlB]

Hi @Anonymous 

This is best done in Power Query. Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwNFTSUXKLghEhqYlFIYkFSrE6IEkjI6CQuweMCCjKzCtJTYFKGhsDhbx9gAQQZZak5irk5ZcoFKUWlmYWpaYo6Co4+ftClZqYAJWEOUGUOpXmpeSkFiSmFkFlTU2Bos5OcCKxOFUhJzEpJ1UpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Itemcode = _t, #"Reference Bom" = _t, #"Itemcode Bom" = _t, Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Itemcode", Int64.Type}, {"Reference Bom", type text}, {"Itemcode Bom", type text}, {"Description", type text}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Status", each if [Reference Bom] = [Itemcode Bom] then "Item available" else if Text.Contains(Text.Upper([Description]), Text.Upper("item not required")) then "No issues" else "Item not available", type text)
in
    #"Added Custom"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous]

@AlB Thank you so much for your reply 

[ValtteriN]

Hi,

Here is one way to do this:

Measure 25 =

SWITCH(TRUE(),

        IF(IFERROR(

            SEARCH("item not required - BOM",MAX('Table (15)'[Description])),FALSE())=1,TRUE()),"No Issues",

MAX('Table (15)'[Itemcode Bom])=MAX('Table (15)'[Reference Bom]),"Item Available",

MAX('Table (15)'[Itemcode Bom])<>MAX('Table (15)'[Reference Bom]),"Item not Available")


End result:

Edit:

If you want to check for blank in the first case you can modify the dax like this:

Measure 25 =

SWITCH(TRUE(),

        AND(IF(IFERROR(

            SEARCH("item not required - BOM",MAX('Table (15)'[Description])),FALSE())=1,TRUE()),

            ISBLANK(MAX('Table (15)'[Itemcode Bom]))),

            "No Issues",

MAX('Table (15)'[Itemcode Bom])=MAX('Table (15)'[Reference Bom]),"Item Available",

MAX('Table (15)'[Itemcode Bom])<>MAX('Table (15)'[Reference Bom]),"Item not Available")

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[Anonymous]

@ValtteriN  Thank you so much for reply