Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Compare the value multiple column

trying to compare the multiple columns to find  item available and item not avaialbe    Itemcode       Reference Bom           Itemcode Bom           Description   1011                    FZ      ...
"Need Help"
Help me
Help me please
Help with DAX
  • DimaMD's avatar
    DimaMD
    3 years ago

    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")

AlB's avatar
AlB
Community Champion
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

AlB Thank you so much for your reply