How to translate SQL to DAX && 'table'[PK_Inv#] like '_____'

[Sparkeltje]

Hello guys,

Hope you can help me, I'm looking for the dax equivelament for like in SQL, in this case I want DAX only to CALCULATE if investments are with 5 positions. 

Investments =

VAR FINYEAR = SELECTEDVALUE('table'[Financial_Year])

RETURN

CALCULATE(SUMX(FILTER('table',

'table'[Source] = "PL"

-- && 'table'[PK_Inv#] like '_____'

&& 'table'[Type] IN {"1", "2"}),

'table'[costs]) ,

'table'[Financial_Year] = FINYEAR,

'table'[Financial_Year])

---
Thank you! 

[v-cazheng-msft]

Hi @Sparkeltje 

Is your problem solved?

Best Regards

Caiyun Zheng

[Sparkeltje]

Yes. Thank you! 

[v-cazheng-msft · Accepted solution]

Hi @Sparkeltje 

You can use LEN function to get the investments with 5 positions.

InvInvestments =

VAR FINYEAR =

    SELECTEDVALUE ( 'table'[Financial_Year] )

RETURN

    CALCULATE (

        SUMX (

            FILTER (

                'table',

                'table'[Source] = "PL"

                    && LEN ( 'table'[PK_Inv#] ) = 5

                    && 'table'[Type] IN { "1", "2" }

            ),

            'table'[costs]

        ),

        'table'[Financial_Year] = FINYEAR,

        'table'[Financial_Year]

)

For more details about this function, you can refer LEN.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sparkeltje]

Problem is that calculation is needed only from the 2 investment numbers that have 5 positions. I don't get that working with contains or containsstring.

10624-VA06-360

10521-VA05-360

10500-VA04-60

11269

11281

10538-VA02-180

[Jihwan_Kim]

Hi, @Sparkeltje 

Please try to use contains or containsstring DAX function.

Or, please have a look at the below article, if that is what you needed.

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

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.