How to check if a string has a certain suffix in a column

[MYDATASTORY]

Hi

Kindly help with a suggestion on DAX, I have a column(DummyDataCarID) and I would like to check if that column has  A hyphen (-)  and suffixes, and if yes then 1 and if no then 0. Please note dummy data has bee used for example.

DummyDataCarID                        Expected answer 

4645-673                                     1

7879-785                                     1

7899-ETW5                                  1

8906                                             0

8780                                             0

[AlB · Accepted solution]

Hi @MYDATASTORY 

Calc column =
VAR posHyphen_ =
    SEARCH ( "-", Table1[CarID], 1, 0 )
VAR anythingAfterHyphen_ =
    IF ( LEN ( Table1[CarID] ) > posHyphen_, 1, 0 )
RETURN
    IF ( posHyphen_ = 0, 0, anythingAfterHyphen_ )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Pragati11 · Accepted solution]

Hi @MYDATASTORY ,

You can use the following DAX:

containsVal = IF(CONTAINSSTRING(Table1[CarID], "-") = TRUE(), 1, 0)

Replace Table1[CarID] in above DAX with your tablename and column.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[AlB · Accepted solution]

Hi @MYDATASTORY 

Calc column =
VAR posHyphen_ =
    SEARCH ( "-", Table1[CarID], 1, 0 )
VAR anythingAfterHyphen_ =
    IF ( LEN ( Table1[CarID] ) > posHyphen_, 1, 0 )
RETURN
    IF ( posHyphen_ = 0, 0, anythingAfterHyphen_ )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[MYDATASTORY]

Thanks to your help, I have tried this seems to work but it does not check if there is suffix after the hyphen basically I also want to check if there is suffix after hyphen which can be 6889-DSG3 OR  6889-454, DAX provides is catching the hyphen but it will need to check if there is suffix after the hyphen. Please note this is dummy data.

[AlB]

Hi @MYDATASTORY 

It would help if you mention who you're answering to. You say "it will need to check if there is suffix after the hyphen". What on earth does that mean exactly? My code checks whether there is something (doesn't matter what) after the hyphen. What do you want to check exactly. A specific suffix? Only DSG3 or 454? Are those the only two suffixes you are interested in (after the hyphen)?? If so:

Calc column =
IF( SEARCH ( "-DSG3", Table1[CarID], 1, 0 )>0 || SEARCH ( "-454", Table1[CarID], 1, 0 )>0,
    1,
    0 )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[V-pazhen-msft]

@MYDATASTORY 

The idea of previous replied solution is those cells have suffixes always contain a hyphen before the suffix. So you can just check whether a cell contains a hyphen. Please update the sample how is your original data included the suffixes in the cell.

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

[VijayP]

@MYDATASTORY 

Column = IF(CONTAINSSTRINGEXACT('Table'[Text],"-"),1,0)

Create a new column using the above function which gives the result

Regards

Vijay Perepa

If this is solution for your Query please mark this a solution and share your Kudoes

also you can watch my Videos on www.youtube.com/perepavijay