Forum Discussion

nikitak224's avatar
nikitak224
New Member
3 years ago
Solved

Need help! How to create a filter based on groups with the same values

I am trying to create a filter of groups based on values of an existing field (States). 

 

These are the values in the Field, State: NJ, CT, TX, AR, CA, None

 

Group 1: All States (NJ, CT, TX, AR, CA)

Group 2: None

Group 3: CT, TX, AR, CA (Excluding NJ)

Group 4: NJ

 

I tried to create groups function, but since NJ is used in multiple groups, that wasn't working.

Any help would be appreciated. Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  nikitak224 ,

    I created some data:

    Here are the steps you can follow：

    1. Enter data – Create a table.

    2. Create measure.

    Flag =
var _select=SELECTCOLUMNS('Table',"1",[State])
VAR _SELECT1=SELECTEDVALUE('Table2'[Group])
return
SWITCH(
    TRUE(),  
    AND(_SELECT1="Group1" , "NJ" in _select || "CT" in _select || "TX" in _select || "AR" in _select ||"CA"  IN _select ),1,
    _SELECT1="Group2" ,1,
    AND(_SELECT1= "Group3" , NOT("NJ")  IN _select),1,
   AND(_SELECT1="Group4" , "NJ" IN _select),1,0
)

    3. Place [Flag]in Filters, set is=1, apply filter.

    4. Result:

    Select the corresponding Slicer to display the corresponding rules

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  nikitak224 ,

    I created some data:

    Here are the steps you can follow：

    1. Enter data – Create a table.

    2. Create measure.

    Flag =
var _select=SELECTCOLUMNS('Table',"1",[State])
VAR _SELECT1=SELECTEDVALUE('Table2'[Group])
return
SWITCH(
    TRUE(),  
    AND(_SELECT1="Group1" , "NJ" in _select || "CT" in _select || "TX" in _select || "AR" in _select ||"CA"  IN _select ),1,
    _SELECT1="Group2" ,1,
    AND(_SELECT1= "Group3" , NOT("NJ")  IN _select),1,
   AND(_SELECT1="Group4" , "NJ" IN _select),1,0
)

    3. Place [Flag]in Filters, set is=1, apply filter.

    4. Result:

    Select the corresponding Slicer to display the corresponding rules

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly