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, ...
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

     

     

NickProp28's avatar
NickProp28
Post Partisan
6 years ago

Hi all,

amitchandak mahoneypat  Anonymous ,

 

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.

 

 

Anonymous's avatar
Anonymous
Not applicable
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