Forum Discussion

romoguy15's avatar
romoguy15
Icon for Helper IV rankHelper IV
4 years ago
Solved

Create Custom Group By Filter for Different Scenarios

Hello, I have a list of codes in a column. I am trying to group this list of codes into three different groups. The problem I am running into is the groups each all contain the same values but slightly different groups and there for, I have to create the grouping to be all separte. I would like to to have all these three buckets into one filter. For example, below are my codes. My first bucket or group, I want to show All Codes, the next bucket, I want it to show me only BNG, CBT, DBP. My last group, I want it to show me only BNG and CBT. 

 

I also have some sample data
https://1drv.ms/u/s!AqID1H0nHPOzhBBrARqPZO1Lfyjv?e=c9SeKG

 

 

The way I have it set up now is it is basically three separate groups in three separate filters which is not very ideal. 

 

Thank you in advance for any help.

Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, romoguy15 

     

    You can try the following methods.

    Measure:

    Measure =
IF (
    SELECTEDVALUE ( 'Table'[Code] ) = "BNG",
    1,
    IF (
        SELECTEDVALUE ( 'Table'[Code] ) = "CBT",
        1,
        IF ( SELECTEDVALUE ( 'Table'[Code] ) = "DBP", 1, 0 )
    )
)

    Place this Measure in the Code slicer's view and set it to equal 1.

    In this slicer, not selecting any of them is All Codes. Hold down CTRL to select BNG and CBT, or BNG, CBT and DBP. You can achieve one slicer to filter three cases.

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

5 Replies

  • romoguy15's avatar
    romoguy15
    Icon for Helper IV rankHelper IV
    4 years ago

    Hi CNENFRNL , unfortunately this method only works in the sample data environment. When I bring in the formula to my real report it get the MdxScript error. The codes I am using are not actually put into a table to view by row. The codes just filter a simple table that has calculation totals by column. Would it help If I changed the sample file to include much more info and try to replicate the exact table I am referring to?

     

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, romoguy15 

     

    You can try the following methods.

    Measure:

    Measure =
IF (
    SELECTEDVALUE ( 'Table'[Code] ) = "BNG",
    1,
    IF (
        SELECTEDVALUE ( 'Table'[Code] ) = "CBT",
        1,
        IF ( SELECTEDVALUE ( 'Table'[Code] ) = "DBP", 1, 0 )
    )
)

    Place this Measure in the Code slicer's view and set it to equal 1.

    In this slicer, not selecting any of them is All Codes. Hold down CTRL to select BNG and CBT, or BNG, CBT and DBP. You can achieve one slicer to filter three cases.

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

    • romoguy15's avatar
      romoguy15
      Icon for Helper IV rankHelper IV
      4 years ago

      I think this is a good workaround to achieve. For some reason, bringing the filter into the real data, it just doesn't filter the table because there are calculation sums in the table that won't change with the filter. I believe that is because the calculations are sums but have filters within those sums.