DAX does not recognise underscore as an argument

[allondon]

I have a column BUSINESS_RULE_REF in a merged Power BI table that contains values like this:

105_AL_Subgroup_CIRCE_Consistency.

Each token between the underscores can be of a different length, I need the text between the first 2 underscores (AL in the example above).

Category =

VAR FirstUnderscore =

    SEARCH("_", 'Append ALL'[BUSINESS_RULE_REF], 1, -1)

VAR SecondUnderscore =

    SEARCH("_", 'Append ALL'[BUSINESS_RULE_REF], FirstUnderscore + 1, -1)

RETURN

    IF(

        FirstUnderscore > 0 && SecondUnderscore > FirstUnderscore,

        MID(

            'Append ALL'[BUSINESS_RULE_REF],

            FirstUnderscore + 1,

            SecondUnderscore - FirstUnderscore - 1

        ),

        BLANK()

    )

Copilot test is fine, in Power BI it fails: An argument of function 'SEARCH' has the wrong data type or has an invalid value.

If I remove -1 as the fourth argument it says the text given to SEARCH function is not found.

Thanks in advance for any ideas.

[allondon]

The solution proivided by grazitti_sapna worked, though I'm still trying to understand how TRIM made the difference. Thanks everyone, I'll try other suggestions as well.

[allondon]

It worked, thanks! Now I need to understand why 🙂

[Ahmedx]

and this code try

Category = 
VAR _FirstUnderscore =SUBSTITUTE( SUBSTITUTE(
 SUBSTITUTE('Append ALL'[BUSINESS_RULE_REF],"_","|"),"||","|"),"||","|")
VAR _ST = PATHITEM(_FirstUnderscore,2,TEXT)
RETURN
_ST

 

[mh2587]

Category = //Try SUBSTITUTE()with PATHITEM() might help you
PATHITEM(SUBSTITUTE('Append ALL'[BUSINESS_RULE_REF], "_", "|"), 2)

[Ahmedx]

try this code

[grazitti_sapna · Accepted solution]

Hi @allondon,

The fourth argument (not_found_value) is optional. Using -1 is fine, but if the first underscore doesn’t exist, SEARCH will return -1, which breaks the second search.

Try below DAX

Category =
VAR TextValue = TRIM( 'Append ALL'[BUSINESS_RULE_REF] ) -- ensure text
VAR FirstUnderscore = SEARCH("_", TextValue, 1, 0) -- 0 if not found
VAR SecondUnderscore =
IF(
FirstUnderscore > 0,
SEARCH("_", TextValue, FirstUnderscore + 1, 0),
0
)
RETURN
IF(
FirstUnderscore > 0 && SecondUnderscore > FirstUnderscore,
MID(
TextValue,
FirstUnderscore + 1,
SecondUnderscore - FirstUnderscore - 1
),
BLANK()
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!