cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
joshikakrishna Occasional Visitor
Occasional Visitor

DAX equivalent of SQL filter not LIKE [0-9] [%a-z%]

Hi,

I nee apply below filter in dax query.

Please help to accomplish this requirement.

How to apply filter with not like [%A-Z%] in DAX (SSAS tabular) + powber BI

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: DAX equivalent of SQL filter not LIKE [0-9] [%a-z%]

HI @joshikakrishna,

 

You can use below calculate column formula to create a variable dictionary table to compare with current text column:

Contains Text = 
VAR dict =
    SELECTCOLUMNS (
        GENERATESERIES ( 97, 122, 1 ),
        "UPPER", UNICHAR ( [Value] ),
        "LOWER", LOWER ( UNICHAR ( [Value] ) )
    )
RETURN
    COUNTROWS (
        FILTER (
            dict,
            SEARCH ( [UPPER], [Text], 1, -1 ) > 0
                || SEARCH ( [LOWER], [Text], 1, -1 ) > 0
        )
    )
    > 0

 

18.PNG

 

In addition, you can also take a look at following blog which mentioned dax string comparison:

From SQL to DAX: String Comparison

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
2 REPLIES 2
Community Support Team
Community Support Team

Re: DAX equivalent of SQL filter not LIKE [0-9] [%a-z%]

HI @joshikakrishna,

 

You can use below calculate column formula to create a variable dictionary table to compare with current text column:

Contains Text = 
VAR dict =
    SELECTCOLUMNS (
        GENERATESERIES ( 97, 122, 1 ),
        "UPPER", UNICHAR ( [Value] ),
        "LOWER", LOWER ( UNICHAR ( [Value] ) )
    )
RETURN
    COUNTROWS (
        FILTER (
            dict,
            SEARCH ( [UPPER], [Text], 1, -1 ) > 0
                || SEARCH ( [LOWER], [Text], 1, -1 ) > 0
        )
    )
    > 0

 

18.PNG

 

In addition, you can also take a look at following blog which mentioned dax string comparison:

From SQL to DAX: String Comparison

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
kazael Regular Visitor
Regular Visitor

Re: DAX equivalent of SQL filter not LIKE [0-9] [%a-z%]

@joshikakrishna

I would recommend using the search function (https://dax.guide/search/). 

You can post an example of your data and I'll try to help