Forum Discussion
Replace or Lookup Values
Try like this. In Switch move all Exact values first. If true it will not check others
Flag = SWITCH(TRUE(),Sheet1[AccType]="X1", "Silver Account" ,Sheet1[AccType]="S4", "Red Account" ,SEARCH("X1",Sheet1[AccType],1,0)>0,"Blue Account" ,SEARCH("X2",Sheet1[AccType],1,0)>0,"Bronze Account" ,SEARCH("S40",Sheet1[AccType],1,0)>0,"Yellow Account" ,"Misc Account")This means if the first value is true it will not check second. So just order in that manner.
Take the updated file from dropbox link again
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
Try
Account Name= SWITCH(TRUE(),SEARCH("X1",Sheet1[AccType],1,0)>0,"Silver Account",SEARCH("X2",Sheet1[AccType],1,0)>0,"Bronze Account","Misc Account")
pbix:https://www.dropbox.com/s/mpdu188qw33eibv/accountType.pbix?dl=0
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
Thanks heaps... I have just tried this logic, but I have a scenario where it does not always work.
e.g. I have another account type of X12 (and subsequently X12.*) which is another account type.
I almost need a formula than will provide something like...Any value is exactly "X1" then 'Sivler Account' and "X1.*" then 'Silver Account'
Then would be able to do other Account Types by following the same style of formula.Sorry I should have mentioned that in my original message... I have others such as X4 type accounts that are different to an X40.
I have a reference table which I thought might have been an option as a simple lookup and I could just update the reference table instead of the formula if there is ever an extra account type added.
The Reference table looks something like this...
Acct Type Account Name X1 Silver Account X20 Bronze Account X12 Blue Account M2 Misc Account S4 Red Account S40 Yellow Account Try like this. In Switch move all Exact values first. If true it will not check others
Flag = SWITCH(TRUE(),Sheet1[AccType]="X1", "Silver Account" ,Sheet1[AccType]="S4", "Red Account" ,SEARCH("X1",Sheet1[AccType],1,0)>0,"Blue Account" ,SEARCH("X2",Sheet1[AccType],1,0)>0,"Bronze Account" ,SEARCH("S40",Sheet1[AccType],1,0)>0,"Yellow Account" ,"Misc Account")This means if the first value is true it will not check second. So just order in that manner.
Take the updated file from dropbox link again
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin