Forum Discussion

Sparkeltje's avatar
Sparkeltje
Frequent Visitor
5 years ago
Solved

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! 
Need Help
  • v-cazheng-msft's avatar
    v-cazheng-msft
    5 years ago

    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.

     

     

5 Replies

  • Sparkeltje's avatar
    Sparkeltje
    Frequent Visitor
    5 years ago

    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

     

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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.