Forum Discussion
markefrody
Post Patron
Post PatronSetting 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" ...
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"))
ryan_mayu
Super User
Super Usernot 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"))- markefrody
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.- ryan_mayu
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"))