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.
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.
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!
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...
Thank you so much for the detailed answer! We are getting close, the DAX formula seems to be the solution but is it correct that it can only contain 2 different outputs? It's always does contain or not contain. The results I need are the differrent "flavours" I know beforehand, so it has to be more than 2 of them.
The automatically generated table option seems to not have this problem. Could you please explain me how to create this filtered table? You've helped me so much already, thanks!It's always does contain or not contain. The results I need are the differrent "flavours" I know beforehand, so it has to be more than 2 of them.
Using nested if condition or SWITCH function, we can populate as many values as we want in a field. But in your cases, the GroupCode has more than one flavour in it. Therefore, you are forced to give only two outputs.
The automatically generated table option seems to not have this problem. Could you please explain me how to create this filtered table? You've helped me so much already, thanks!
I can help you with the power query. For that, please post the autogenerated powerquery of your existing table. In case you don't know, it is available at Transform->Advanced Editor.