Forum Discussion
Problems with creating some custom columns
Try to use this:
1st approver = IF ( 'Table'[Scanned] = "" && 'Table'[Last Approver] = "Basware Matching", "1st pass mactching", BLANK () )Hi KellyLen,
Try this formula:
PO = IF ( 'Table'[Company code] = "FI24" && 'Table'[Purchase Category (ext)] = "Technical Maintenance" && 'Table'[Purchase Sub Category] <> "Logistics" && ( LEFT ( 'Table'[Gift Card/Traffic Type/Job No], 1 ) IN { "F", "P" } || LEFT ( 'Table'[Gift Card/Traffic Type/Job No], 2 ) IN { "BF" } ), "PO", BLANK () )Should give the result below:
Regards,
MFelix
Hi KellyLen,
Try this formula:
PO =
IF (
'Table'[Company code] = "FI24"
&& 'Table'[Purchase Category (ext)] = "Technical Maintenance"
&& 'Table'[Purchase Sub Category] <> "Logistics"
&& (
LEFT ( 'Table'[Gift Card/Traffic Type/Job No], 1 ) IN { "F", "P" }
|| LEFT ( 'Table'[Gift Card/Traffic Type/Job No], 2 ) IN { "BF" }
),
"PO",
BLANK ()
)Should give the result below:
Regards,
MFelix
Thank you very much! It worked.
Now I have another difficulty to combine two columns. I firstly created a conditional column in Query Editor:
Table.AddColumn(#"Replaced Value", "PO/ No PO", each if Text.StartsWith([Document header text], "14F") then "PO" else if Text.EndsWith([Document header text], "JFK") then "PO" else if [Document type] = "YC" then "PO" else if [Document type] = "YO" then "PO" else "No PO")
I would like to get these conditions to your given formula as well so all these would end up in one column as values PO or No PO.
Are you able to help me with this as well? :)
Best regards,
Kelly
Hi KellyLen,
Try this formula:
PO = IF ( 'Table'[Company code] = "FI24" && 'Table'[Purchase Category (ext)] = "Technical Maintenance" && 'Table'[Purchase Sub Category] <> "Logistics" && ( LEFT ( 'Table'[Gift Card/Traffic Type/Job No], 1 ) IN { "F", "P" } || LEFT ( 'Table'[Gift Card/Traffic Type/Job No], 2 ) IN { "BF" } ) && ( LEFT ( 'Table'[Document header text], 3 ) IN { "14F" } || RIGHT ( 'Table'[Document header text], 3 ) IN { "JFK" } ) && 'Table'[Document header text] IN { "YC", "YO" }, "PO", BLANK () )The part in bold was the one I added the question is if all of this information you need to have AND / OR if it's AND it's like is if is OR you should replace the && by ||
Regards,
MFelix
I cannot thank you enough. :) It works perfectly if I replaced the && with ||.
What if I would like to add there a condition that Document header text contains "IKL"? How should the containing work as I understand that LEFT works for beginning and RIGHT for ending?
Best regards,
Kelly
Hi MFelix,
Could you please help me again?
I have created a column, where I use LOOKUPVALUE to get the needed values. Right now the DAX is following:
Contract status = LOOKUPVALUE('Processed CLM data'[Status]; 'Processed CLM data'[Vendor number]; 'Contract coverage'[Vendor number])I would like to add here a condition that if in column "Category" there is value "Tour" and in column "Profit" value "12201" and in column "Account" values "123" and "345" then it would mark the status as "First". Otherwise it would use the lookupvalue condition.
Are you able to help?
Best regards,
Kelly
Hi KellyLen,
Assuming that you columns are on the same you need to add something like this:
Status = IF ( Table[Category] = "Tour" && Table[Profit] = 12201 && Account IN { 123, 345 }, "First", LOOKUPVALUE ( 'Processed CLM data'[Status], 'Processed CLM data'[Vendor number], 'Contract coverage'[Vendor number] ) )Made this without having any knowledge of the columns and tables where they are.
Regards,
MFelix