Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sparkeltje
Frequent Visitor

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

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! 
1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
Community Support

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.

 

 

View solution in original post

5 REPLIES 5
v-cazheng-msft
Community Support
Community Support

Hi @Sparkeltje 

Is your problem solved?

 

Best Regards

Caiyun Zheng

Yes. Thank you! 

v-cazheng-msft
Community Support
Community Support

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
Frequent Visitor

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
Super User
Super User

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.

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


Go to My LinkedIn Page


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.