Problems with creating some custom columns

[KellyLen]

Hello,

I have a problem creating some columns.

Firstly, I would like to create column, which tells me that if there is "blank" in column Scanner ID and value "Basware Matching" in column Last Approver, then it would be value "1st pass matching". 

And then I would like to create column, which tells me if there is "FI24" in Company Code column and "Technical Maintenance" in Purchase Category column and anything else but "Logistics" in Purchase Sub Category column and in Gift Cards column the values are starting with "BF", "L" and "P" then it would be value "PO". 

I would be very grateful, if you could help me out with creating these daxes. 

Best regards,

Kelly 

[MFelix · Accepted solution]

Try to use this:

1st approver =
IF (
     'Table'[Scanned] = ""
        && 'Table'[Last Approver] = "Basware Matching",
    "1st pass mactching",
    BLANK ()
)

[MFelix]

Hi  @KellyLen,

You need to have this two calculated columns:

1st approver =
IF (
    ISBLANK ( 'Table'[Scanned] )
        && 'Table'[Last Approver] = "Basware Matching",
    "1st pass mactching",
    BLANK ()
)

Not really sure how you want to setup your second column you have 4 columsn to check values:

Company - = FI24

Purchase Category - = Technical Maintenance

Purchase Sub Category - <> Logistics

Gift Cards - Starts with BF L and P

value should be PO

Is this it?

Regards,

MFelix

[KellyLen]

@MFelix

I tried to use your given dax for 1st pass matching and unfortunately it did not work. Although it seems to be logical and it does not give me any error, there will not be any values in created column. 

And about the second thing, you got it right, I need to check values in 4 columns. 

Best regards,

Kelly

[MFelix · Accepted solution]

Try to use this:

1st approver =
IF (
     'Table'[Scanned] = ""
        && 'Table'[Last Approver] = "Basware Matching",
    "1st pass mactching",
    BLANK ()
)

[KellyLen]

Thanks a lot, this worked! 🙂 

How about the other problem? 🙂

Best regards,

Kelly 

[MFelix]

That was the easy question :D:D

Can you supply any mockup of your data?

Regards,

MFelix 

[KellyLen]

😄 

I do not know, how could I share my data with you? I can only share a screenshot of it:

 Kelly

[MFelix · Accepted solution]

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

[KellyLen]

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 

[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" }
        )
        && (
            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

[KellyLen]

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 

[MFelix]

Hi @KellyLen,

The formula should be something like this:

SEARCH( "IKL", 'Table'[Document header], 1 , 0 ) > 0 

This should be added to your calculation.

Regards,

MFelix

[KellyLen]

Thank you very much, @MFelix!! 🙂 

[KellyLen]

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 

[MFelix]

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

[KellyLen]

@MFelix Super! It worked! Thanks a lot again! 🙂 

Best regards,

Kelly