Forum Discussion

inbal's avatar
inbal
Regular Visitor
8 years ago

using one slicer for filtering data from more than one column

I will be happy to know if there any way to create a filter on more than one columns?
 
For example:

I have a table with 3 true/false columns (eg A, B, C). I want to create one filter where the user can control the rows presented.

In other words, if the user will click on A - he will see all the rows in which it the value in column A marked as true without dependents on the marking of columns B and C.
Or if the user will click on A + B - he will see all the rows in which values A and B are true, regardless to column C, and so on.
 
I understand that I can create 3 different slicers as a simple solution, but because it is a problem that is repeated in several configurations, maybe someone has any idea for a "smarter solution"?
And also, it will be great if it can be supported in the sync slicers as the simple slicer (of one column) :)
 
Thanks,
Inbal
Need Help

3 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Inbal,

     

    If you don't have other conditions, this is possible. Please check out the demo in the attachment.

    1. Create a INDEPENDENT table. 

    Column1

    A
    B
    C
    AB
    AC
    BC
    ABC

    2. Create a measure.

    Measure =
VAR selected =
    IF ( HASONEVALUE ( 'Table2'[Column1] ), MIN ( 'Table2'[Column1] ), BLANK () )
RETURN
    IF (
        ISBLANK ( selected ),
        1,
        IF (
            selected = "A"
                && SELECTEDVALUE ( 'Table1'[A] ) = TRUE (),
            1,
            IF (
                selected = "B"
                    && SELECTEDVALUE ( 'Table1'[B] ) = TRUE (),
                1,
                IF (
                    selected = "C"
                        && SELECTEDVALUE ( 'Table1'[C] ) = TRUE (),
                    1,
                    IF (
                        selected = "AB"
                            && SELECTEDVALUE ( 'Table1'[A] ) = TRUE ()
                            && SELECTEDVALUE ( 'Table1'[B] ) = TRUE (),
                        1,
                        IF (
                            selected = "AC"
                                && SELECTEDVALUE ( 'Table1'[A] ) = TRUE ()
                                && SELECTEDVALUE ( 'Table1'[C] ) = TRUE (),
                            1,
                            IF (
                                selected = "BC"
                                    && SELECTEDVALUE ( 'Table1'[C] ) = TRUE ()
                                    && SELECTEDVALUE ( 'Table1'[B] ) = TRUE (),
                                1,
                                IF (
                                    selected = "ABC"
                                        && SELECTEDVALUE ( 'Table1'[A] ) = TRUE ()
                                        && SELECTEDVALUE ( 'Table1'[B] ) = TRUE ()
                                        && SELECTEDVALUE ( Table1[C] ) = TRUE (),
                                    1,
                                    0
                                )
                            )
                        )
                    )
                )
            )
        )
    )

    3. Add it to the Visual Level filter. Don't need to add to the visual.

    using_one_slicer_for_filtering_data_from_more_than_one_column

     

     

    Best Regards,

    Dale

    • inbal's avatar
      inbal
      Regular Visitor
      8 years ago

      Hi Dale,

       

      THANK YOU for the answer, but unfortunately, this solution is not possible for me. :smileysad:
      The idea is that external user will see only 3 options that he can choose from (checkbox: A,B,C) , and not all 9 (in the feature it will be 16=4 ^ 2).

      Also, I do not need to show only one parameter, but to display rows in tables that meet the conditions.

       

      Thanks,

      Inbal

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi Inbal,

         

        Can you modify my solution? I attached the file. Or how can I modify it to satisfy your goal?

         

        Best Regards,

        Dale