Forum Discussion
Filter value from column with multiple values
If you know in advance about all such cases, you could handle it as follows...
Flavour = VAR CheckOV = SEARCH("OV",FlavourData[Groupcode],1,BLANK()) VAR Check2 = SEARCH("2",FlavourData[Groupcode],1,BLANK()) VAR CheckX = SEARCH("X",FlavourData[Groupcode],1,BLANK()) VAR CheckOH = SEARCH("OH",FlavourData[Groupcode],1,BLANK()) VAR CheckH = SEARCH("H",FlavourData[Groupcode],1,BLANK()) VAR Result = SWITCH( TRUE(), NOT(ISBLANK(CheckOV)),"Contains OV", NOT(ISBLANK(Check2)),"Contains 2", NOT(ISBLANK(CheckX)),"Contains X", NOT(ISBLANK(CheckH)) && ISBLANK(CheckOH),"Contains H", NOT(ISBLANK(CheckOH)),"Contains OH", "Wrong Value" ) RETURN ResultIf you don't know about all such cases, then you have to figure out some other way.
Flavour =
VAR CheckOV = SEARCH("OV",FlavourData[Groupcode],1,BLANK())
VAR Check2 = SEARCH("2",FlavourData[Groupcode],1,BLANK())
VAR CheckX = SEARCH("X",FlavourData[Groupcode],1,BLANK())
VAR Result =
SWITCH(
TRUE(),
NOT(ISBLANK(CheckOV)),"Contains OV",
NOT(ISBLANK(Check2)),"Contains 2",
NOT(ISBLANK(CheckX)),"Contains X",
"Wrong Value"
)
RETURN Result
This is awesome! It seems to be working, although with one caveat. When I have 2 values; example: OH and H. With the H selection also OH will show up because it has the letter H in it. Is there somehow any way to combat this to? If this isn't possible I'll find another way around it. But if this small issue could also be resolved this would work magically for me!
If you know in advance about all such cases, you could handle it as follows...
Flavour = VAR CheckOV = SEARCH("OV",FlavourData[Groupcode],1,BLANK()) VAR Check2 = SEARCH("2",FlavourData[Groupcode],1,BLANK()) VAR CheckX = SEARCH("X",FlavourData[Groupcode],1,BLANK()) VAR CheckOH = SEARCH("OH",FlavourData[Groupcode],1,BLANK()) VAR CheckH = SEARCH("H",FlavourData[Groupcode],1,BLANK()) VAR Result = SWITCH( TRUE(), NOT(ISBLANK(CheckOV)),"Contains OV", NOT(ISBLANK(Check2)),"Contains 2", NOT(ISBLANK(CheckX)),"Contains X", NOT(ISBLANK(CheckH)) && ISBLANK(CheckOH),"Contains H", NOT(ISBLANK(CheckOH)),"Contains OH", "Wrong Value" ) RETURN ResultIf you don't know about all such cases, then you have to figure out some other way.
I also learned a lot from this!
Talk about learning, here is one more thing to learn about the SWITCH() function. This function terminates when the condition returns TRUE for the 1st time and it won't execute any further. You can actually leverage this "knowledge" to handle the situation just by adjusting the order of the conditions in SWITCH(). See the results below...
Apparently, both are the same formula, but different results. Usually, I don't recommend it unless the user completely understands how the function works rather than just knowing the syntax of a function.
Yes. You can use it in your scenario. But if you are not careful in your ordering of conditions, you might get the wrong result and the end-user won't even realize it. That is why I suggested the explicit multi-condition using && operator. If you explicitly say that I want "H" but don't want "OH" in the condition, it will always work irrespective of the ordering. Be careful.
Thank you so much man! These cases I should know beforehand. I can make it work this way. Thank you so much for your time and this solution. I also learned a lot from this!
That is awesome! And could possible indeed also work for my reasoning. The SWITCH function will execute to a true result (or eventually false) each unique row right? If so this will work for my purpose!
Thanks for the explaination and all your help!