Forum Discussion
Setting Up Conditional Column Using Alpha Numeric ID
Hi Sirs,
I am trying to create a "Classification" column wherein I try to classify the following alpha numeric:
1.) If ID starts with "MBKX" classify as "Private"
2.) If ID starts with "SMIX" and then followed by 5 or more digits classify as "Private". (examples: SMIX12345 or SMIX123456 or SMIX1234567...)
3.) If ID starts with "SMIX" and then followed by 4 digits only classify as "Public". (examples: SMIX1234 or SMIX9471)
4.) If ID does not fall under category #1, #2, or #3, classify as "Public".
Here is an example on how it should look like:
Appreciate your kind assistance.
Best regards,
Mark V
you can try this to create a measure
Measure = var _mbkx=IFERROR(find("MBKX",MAX('Table'[ID])),0) var _smix=IFERROR(find("SMIX",MAX('Table'[ID])),0) var _sub=len(SUBSTITUTE(MAX('Table'[ID]),"SMIX","")) return if(_mbkx>0,"Private",if(_smix>0&&_sub>=5,"Private","Public"))
4 Replies
not sure if the real data is more complicated or not, maybe ID is something like SMIX12aab22
if not, I think you can combine the senario 3 and 4 because they return the same result
Column = var _mbkx=IFERROR(find("MBKX",'Table'[ID]),0) var _smix=IFERROR(find("SMIX",'Table'[ID]),0) var _sub=len(SUBSTITUTE('Table'[ID],"SMIX","")) return if(_mbkx>0,"Private",if(_smix>0&&_sub>=5,"Private","Public"))Hi ryan_mayu. Thank you for your assistance. I have tried using the DAX code but it seems not to work on my side. "IFERROR" seems to be not recognized. Did you create "New Measure" or "New Column" for the DAX?
By the way, I am using Storage Mode: Direct Query.you can try this to create a measure
Measure = var _mbkx=IFERROR(find("MBKX",MAX('Table'[ID])),0) var _smix=IFERROR(find("SMIX",MAX('Table'[ID])),0) var _sub=len(SUBSTITUTE(MAX('Table'[ID]),"SMIX","")) return if(_mbkx>0,"Private",if(_smix>0&&_sub>=5,"Private","Public"))
Hi guys. Any other suggestions? Thanks.