Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Custom Slicer based on Grouped Cell Values in a column

Hello,

 

I am trying to create custom slicers that allow me to select a "category" instead of a cell value. 

 

This is what I have currently:

abc
a1b1c1
a2b2c2
a3b3c3
a4b4c4
a5b5c5
a6b6c6

 

Suppose b1, b2, b5 belong to "Category 1", b3 and b6 belong to "Category 2", and b4 is "Category 3". I want to create a slicer that has 3 options: Categories 1, 2, and 3, such that it filters my table based on the selected category/categories. 

 

For example, if I select Category 1, the output should be: 

abc
a1b1c1
a2b2c2
a5b5c5

 

How can I achieve this?

 

Thank you. Really appreciate your help!

"DAX "
"Need Help"
Power Query
  • Irwan's avatar
    Irwan
    2 years ago

    hello Anonymous 

     

    please check if this accomodate your need.

     

    1. create a calculated column for categories.

    Category =
    IF(
        RIGHT('Table'[a],1)="1"||
        RIGHT('Table'[a],1)="2"||
        RIGHT('Table'[a],1)="5"||
        RIGHT('Table'[b],1)="1"||
        RIGHT('Table'[b],1)="2"||
        RIGHT('Table'[b],1)="5"||
        RIGHT('Table'[c],1)="1"||
        RIGHT('Table'[c],1)="2"||
        RIGHT('Table'[c],1)="5",
        "Category 1",
    IF(
        RIGHT('Table'[a],1)="3"||
        RIGHT('Table'[a],1)="6"||
        RIGHT('Table'[b],1)="3"||
        RIGHT('Table'[b],1)="6",
        "Category 2",
        "Category 3"
    ))

     

    2. plot your data in table visual and the category column in slicer.

     

    Hope this will help you.

    Thank you.

3 Replies

  • Irwan's avatar
    Irwan
    Icon for Super User rankSuper User
    2 years ago

    hello Anonymous 

     

    please check if this accomodate your need.

     

    1. create a calculated column for categories.

    Category =
    IF(
        RIGHT('Table'[a],1)="1"||
        RIGHT('Table'[a],1)="2"||
        RIGHT('Table'[a],1)="5"||
        RIGHT('Table'[b],1)="1"||
        RIGHT('Table'[b],1)="2"||
        RIGHT('Table'[b],1)="5"||
        RIGHT('Table'[c],1)="1"||
        RIGHT('Table'[c],1)="2"||
        RIGHT('Table'[c],1)="5",
        "Category 1",
    IF(
        RIGHT('Table'[a],1)="3"||
        RIGHT('Table'[a],1)="6"||
        RIGHT('Table'[b],1)="3"||
        RIGHT('Table'[b],1)="6",
        "Category 2",
        "Category 3"
    ))

     

    2. plot your data in table visual and the category column in slicer.

     

    Hope this will help you.

    Thank you.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Yes, this worked perfect. Thank you so much!

      • Irwan's avatar
        Irwan
        Icon for Super User rankSuper User
        2 years ago

        hello Anonymous 

         

        glad to be a help.

         

        Thank you.