Forum Discussion
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 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 .
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")
6 Replies
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.
AlB Thank you so much for your reply
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/ValtteriN Thank you so much for reply
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")DimaMD Thank you so much its working
