Forum Discussion
DAX does not recognise underscore as an argument
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).
Hi allondon,
The fourth argument (
not_found_value) is optional. Using-1is fine, but if the first underscore doesn’t exist,SEARCHwill 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!
6 Replies
Hi allondon,
The fourth argument (
not_found_value) is optional. Using-1is fine, but if the first underscore doesn’t exist,SEARCHwill 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!Category = //Try SUBSTITUTE()with PATHITEM() might help you PATHITEM(SUBSTITUTE('Append ALL'[BUSINESS_RULE_REF], "_", "|"), 2)and this code try
Category = VAR _FirstUnderscore =SUBSTITUTE( SUBSTITUTE( SUBSTITUTE('Append ALL'[BUSINESS_RULE_REF],"_","|"),"||","|"),"||","|") VAR _ST = PATHITEM(_FirstUnderscore,2,TEXT) RETURN _STtry this code
It worked, thanks! Now I need to understand why 🙂
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.
