Need help on Like statement DAX

[NickProp28]

Dear experts,

Based on the table, 
I have two condition need to calculate which is
i.)Only want get the data (Profit) which Origin/Dest is under MYKUL,MYPEN,CNSHA,SGSIN. Other from this, data(Profit) will ignore.
ii.)If Origin=Dest. Data will be ignore too (Example like CNSHA=CNSHA).
With the help, i able get DAX.
=Calculate(sum(table[profit]), filter(Table, table[origin]<>table[destination] && table[origin] in {"MYKUL","MYPEN","CNSHA","SGSIN"}.
But if I want to use LIKE (IN SQL) statement, for example calculate origin column which the start with "MY.." , "CN..".
How the DAX function will be like. 

Hope you will have a great day!
Best thanks.

[harshnathani · Accepted solution]

HI @NickProp28 ,

You can have a look at this

https://www.sqlbi.com/articles/from-sql-to-dax-string-comparison/

Regards,

Harsh Nathani

[NickProp28]

Hi all,

@amitchandak @mahoneypat  @harshnathani ,

I try it and come out with this DAX.

But the calculation is wrong. Can you guys take a look on my DAX.

Count = CALCULATE(SUM(Financial[Job Profit]),FILTER(Financial,Financial[Origin]<>Financial[Dest] && LEFT(Financial[Origin],2) IN {"SG","MY"} && LEFT(Financial[Dest],2) IN {"SG","MY"}))

This is from https://www.sqlbi.com/articles/from-sql-to-dax-string-comparison/
Hope you guys have a great day.

Best thanks.

[harshnathani · Accepted solution]

Hi @NickProp28 ,

I Tried  replicating this with some dummy data. Work well for me.

Count =
CALCULATE (
    SUM ( Financial[Job Profit] ),
    FILTER (
        Financial,
        Financial[Origin] <> Financial[Dest]
            && LEFT (
                Financial[Origin],
                2
            )
                IN {
                "SG",
                "MY"
            }
            && LEFT (
                Financial[Dest],
                2
            )
                IN {
                "SG",
                "MY"
            }
    )
)

Just to troubleshoot ,try to Trim your Origin and Destination Columns in Power Query to removing leading spaces if any.

Regards,

Harsh Nathani

[amitchandak]

@NickProp28 , you can use following

https://docs.microsoft.com/en-us/dax/containsstring-function-dax

https://docs.microsoft.com/en-us/dax/search-function-dax

https://docs.microsoft.com/en-us/dax/find-function-dax

[mahoneypat]

One simple way to do this would be to add a column in query or DAX that shows the first two letters in that column.  Then you can use your same approach/measure with that new column "... in {"MY", "CN"} ..."

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[harshnathani · Accepted solution]

HI @NickProp28 ,

You can have a look at this

https://www.sqlbi.com/articles/from-sql-to-dax-string-comparison/

Regards,

Harsh Nathani