Forum Discussion

MRensenSDR's avatar
MRensenSDR
Regular Visitor
5 years ago
Solved

Filter value from column with multiple values

Hi there!   I'm having trouble figuring out how to solve the following problem. I have a table in which I want to filter on a column. The table contain the following: Example: Groupcode V...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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 Result

    If you don't know about all such cases, then you have to figure out some other way.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.

 

MRensenSDR's avatar
MRensenSDR
Regular Visitor
5 years ago

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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

  • MRensenSDR's avatar
    MRensenSDR
    Regular Visitor
    5 years ago

    Thanks for the explaination and all your help!