Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Assume we have the following column called [ERPCode] below. I want to dynamically return the second or third value within my serial key. If my ERP code contains "MI" return the second value, but if my ERP code contains "SI" then return me the third value in my serial key. A sample output is provided for reference.
Please help.
Thanks,
Mohan V.
Solved! Go to Solution.
Hi @Anonymous ,
You just modify the dax formula provided by @Fowmy .
Please try below dax formula:
Column =
VAR _code = [ERPCode]
VAR _len =
LEN ( _code )
VAR _a =
FIND ( ".", _code )
VAR _b =
IFERROR ( FIND ( ".", _code, _a + 1 ), 0 )
VAR _c =
IFERROR ( FIND ( ".", _code, _b + 1 ), 0 )
VAR _val =
SWITCH (
TRUE (),
CONTAINSSTRING ( _code, "MI" ),
IF ( _b <> 0, MID ( _code, _a + 1, _b - _a - 1 ), MID ( _code, _a + 1, _len ) ),
CONTAINSSTRING ( _code, "SI" ),
IF ( _c <> 0, MID ( _code, _b + 1, _c - _b - 1 ), MID ( _code, _b + 1, _len ) )
)
RETURN
_val
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
You just modify the dax formula provided by @Fowmy .
Please try below dax formula:
Column =
VAR _code = [ERPCode]
VAR _len =
LEN ( _code )
VAR _a =
FIND ( ".", _code )
VAR _b =
IFERROR ( FIND ( ".", _code, _a + 1 ), 0 )
VAR _c =
IFERROR ( FIND ( ".", _code, _b + 1 ), 0 )
VAR _val =
SWITCH (
TRUE (),
CONTAINSSTRING ( _code, "MI" ),
IF ( _b <> 0, MID ( _code, _a + 1, _b - _a - 1 ), MID ( _code, _a + 1, _len ) ),
CONTAINSSTRING ( _code, "SI" ),
IF ( _c <> 0, MID ( _code, _b + 1, _c - _b - 1 ), MID ( _code, _b + 1, _len ) )
)
RETURN
_val
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous
Will a Power Query solution work for you?
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
Here is the DAX solution, add this as a Calculated Column:
Key =
IFERROR(
VAR __EPR = [ERP Code]
VAR P = LEFT( __EPR , 2 )
VAR P1 = FIND("." , __EPR)
VAR P2 = FIND("." , __EPR , P1 + 1 )
VAR P3 = FIND("." , __EPR , P2 + 1 )
VAR RESULT =
SWITCH(
P,
"MI", MID( __EPR, P1 + 1 , P2 - P1 - 1 ),
"SI", MID( __EPR, P2 + 1 , P3 - P2 - 1 )
)
RETURN
RESULT,
BLANK()
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy It is working but only issue here is if i dont have any delimeter then it is returing blank as in below case.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |