DAX - Finding specific value/text within Column

[FanofExcel18]

I am trying to figure out the correct measure and/or column DAX operation to extract whether another column has a specific string of characters. See example below as to what results I am attempting to achieve.

Example:

Column Data	Results
Testing 1.0 for Deployment	1.0
Testing 2.0 for Deployment	2.0

 

[JustynaMK]

Hi,

Do you always want to return the version number from your string? If yes, and assuming these are always 3 characters long, I'd suggest using the following column (DAX):

Result = 
    var charPos = SEARCH(".", 'Table'[Column Data], 1, 0)
return
    IF(charPos, MID('Table'[Column Data], charPos - 1, 3))

 

[FanofExcel18]

Hi,

Do you always want to return the version number from your string? If yes, and assuming these are always 3 characters long, I'd suggest using the following column (DAX):

Result =
    var charPos = SEARCH(".", 'Table'[Column Data], 1, 0)
return
    IF(charPos, MID('Table'[Column Data], charPos - 1, 3))

View attachment 12081

Does it matter if the character's within the source cell could vary? Meaning cell 1's character length is 20 and the version would be at the 10th character. The 2nd cell would be character length of 30 and version start at 15th character.

 

[JustynaMK]

Good question - the length of the original string ("Column Data") won't matter - variable charPos will still return a proper location of a dot "." character. However, bear in mind that my current code is assuming that:
1. your version number (e.g. 1.0, 2.0) is always 3-characters long
2. there is only one dot "." in the original string ("Column Data").

If you see some unexpected result then feel free to share a screenshot.

 

[FanofExcel18]

Good question - the length of the original string ("Column Data") won't matter - variable charPos will still return a proper location of a dot "." character. However, bear in mind that my current code is assuming that:
1. your version number (e.g. 1.0, 2.0) is always 3-characters long
2. there is only one dot "." in the original string ("Column Data").

If you see some unexpected result then feel free to share a screenshot.

This is perfect thank you! I do have a follow up and I may need to post new.

So I used the DAX column suggested. I've setup a Matrix table with a Name as a Row, Dax Column as a Column, and Values is a Distinct Count of Column 1.

Name	1.0	2.0	3.0
ABC Org	1		1
DEF Org			1
GHI Org		1	1
JKL Org	1	1	1

I would like to simply be able to tell all the Name's that only have 3.0 excluding the ones that have 1.0 or 2.0 including 3.0. I can filter but that doesn't filter the ones that also have 1.0 and 2.0

 

[JustynaMK]

You might want to create a new DAX measure that counts all instances of versions that are not equal to 3.0 -

CountExcl3.0 = 
    CALCULATE(
        COUNT('Table'[Result]),
        FILTER('Table', 'Table'[Result] <> "3.0")
    )

As you can see in this table, DEF Org is not visible -

Now that we know our measure is working correctly, you can apply it to your matrix, i.e. CountExcl3.0 is blank -

 

[FanofExcel18]

You might want to create a new DAX measure that counts all instances of versions that are not equal to 3.0 -

CountExcl3.0 =
    CALCULATE(
        COUNT('Table'[Result]),
        FILTER('Table', 'Table'[Result] <> "3.0")
    )

As you can see in this table, DEF Org is not visible -
View attachment 12092

Now that we know our measure is working correctly, you can apply it to your matrix, i.e. CountExcl3.0 is blank -
View attachment 12093

Unforunately, its not working.

 

[JustynaMK]

Can you share some screenshots along with an example of your dataset? That would help with troubleshooting.

 

[FanofExcel18]

Can you share some screenshots along with an example of your dataset? That would help with troubleshooting.

I figured it out! I was able to add a filter to the visual and has provided me what I exactly needed. Thank you again for your help!

 

[JustynaMK]

glad you figured it out. Take care.