Forum Discussion

NickProp28's avatar
NickProp28
Post Partisan
6 years ago
Solved

Need help on Like statement DAX

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.

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

     

     

5 Replies