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:

GroupcodeValue
BK; WKA; 4; VGM; OVx
BK; WKA; OVx
OVx
BK; WKA; 4; VGMx
BK; WKA; VGM; 6x
OV; 6x

 

In this case only the 'OV' rows are relevant for the enduser. But if the enduser only wants to filter this he/she has to check all the different versions with 'OV' in them to have a correct filter. One option is to replace the values with 'OV' in them to OV so the filter only has the 'OV' option. The problem here is that I don't know all the different combinations beforehand, so this could result in breaking the dashboard or missing data. Is possibly some sort of formule in which I can user a sort of "lookup" function to combat this problem? Thanks!

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.

16 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    You could add another calculated column. Refer to the image above. Then use the new calculated column in filters/slicers.

    Change the IF condition as per your need.

     

    • MRensenSDR's avatar
      MRensenSDR
      Regular Visitor
      5 years ago

      Thanks for the reply, this would seem to do the trick! Is it also possible to extend the search parameter? In this example I only used "OV", but in reality I have a few more "flavours" that have the same problem. Is it possible to do these results in 1 column? Thanks!

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

         

        You can add more OR conditions using || to the IF condition

         

        If you don't want to add so much DAX code, then alternatively you can tell the users to use the advanced filter option of the visuals.

         

        But the options will be limited to 1 or 2 values at a time.

         

        The best option is to automatically generate a table for filtering in PowerQuery, create a relationship and use that PowerQuery generated table for filtering.... Refer to the image below...