Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Help with a filter

Hi, I've a table like this

 

CLIENT_ID     JUNY   JULY    AGOUST

      1                 10       20        25

      2                  5        15        10  

      3                  20       10        5

 

I want to create a filter like this:   JUNY < &VARIABLEINSLICER& || JULY < &VARIABLEINSLICER& || AGOUST < &VARIABLEINSLICER&.  

 

 I want the client could choose the &VARIABLEINSLICER& scrolling a slicer.

 

How can I doi it?

 

Thanks

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi, I've a table like this

     

    CLIENT_ID     JUNY   JULY    AGOUST

          1                 10       20        25

          2                  5        15        10  

          3                  20       10        5

     

    I want to create a filter like this:   JUNY < &VARIABLEINSLICER& || JULY < &VARIABLEINSLICER& || AGOUST < &VARIABLEINSLICER&.  

     

     I want the client could choose the &VARIABLEINSLICER& scrolling a slicer.

     

    How can I doi it?

     

    Thanks

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi Anonymous,

     

    If I understand your requirement correctly that you want to get the output like below.

     

     

    You could use the Parameter slicer to filter the value.

     

    If that is your desired, you could refer to the steps below.

     

    1. Create the What if Parameter under Modeling>> New Parameter then set the default values.

     

    2. Create the measure with the formula below.

     

    Measure =
VAR june =
    CALCULATE (
        MAX ( 'Table1'[Value] ),
        FILTER ( ALLEXCEPT ( Table1, 'Table1'[Client ID] ), 'Table1'[Month] = "June" )
    )
VAR july =
    CALCULATE (
        MAX ( 'Table1'[Value] ),
        FILTER ( ALLEXCEPT ( Table1, 'Table1'[Client ID] ), 'Table1'[Month] = "July" )
    )
VAR august =
    CALCULATE (
        MAX ( 'Table1'[Value] ),
        FILTER ( ALLEXCEPT ( Table1, 'Table1'[Client ID] ), 'Table1'[Month] = "August" )
    )
RETURN
    IF (
        'Parameter'[Parameter Value] > june
            || 'Parameter'[Parameter Value] > july
            || 'Parameter'[Parameter Value] > august,
        1,
        0
    )

    3. Then you could put this measure under visual level filter like below.

     

     

     

    More details, you could refer to the attachment.

     

    If you need further help, please share your desired output so that we could help further on it. 

     

    Best Regards,

    Cherry

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-piga-msft!

       

      Thanks for your answer, Its exactly what I want, but I have one column per month instead of a month column with the june, july...values. I dont have value column and a generic month column. I dont know if I'm explaining myself properly

      Thanks for all! This would be very usefull for me

      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        7 years ago

        Hi Anonymous,

         

        Thanks for your answer, Its exactly what I want, but I have one column per month instead of a month column with the june, july...values. I dont have value column and a generic month column. I dont know if I'm explaining myself properly

        For your scenario, you could change your data model in Query Editor.

         

        Select the per month column and Click Unpivot Columns, then rename the Attribute to Month.

         

        You could find these steps in my attachment for the first reply.

         

        Best  Regards,

        Cherry