Forum Discussion

ifarias's avatar
ifarias
New Member
3 years ago
Solved

AND function on PowerBI slicers

I created a slicer like this on PowerBI, but I would like the values to add up as "AND" conditions, rather than as "OR". For instance, I would like the values returned to be those where Chris Gray AND Carlos Grillo are managers, rather than Chris Gray OR Carlos Grillo are managers. How can I do this?

 

  • Anonymous's avatar
    Anonymous
    3 years ago

    still not sure but try :

     

    Selected Measure Value = 
    SWITCH(SELECTEDVALUE('Measures for Slicer'[Measure])
        , "Managers", [Manager Names]
        , BLANK()
        )

Selected Measure Value (only for managers) = 
    // Get the number of managers you selected
    VAR SelectedmanagerCount = CALCULATE(COUNTROWS(VALUES(table[managers]))
                                        , ALLSELECTED(table))
    VAR output = SUMX (
        VALUES( table[manager] ),    //Get each manager in the current context
        VAR x = ADDCOLUMNS (            //For each manager, set a flag for whether 
                                        //there is data for each selected manager
                    ALLSELECTED ( table ),
                    "Flag", IF ( NOT (ISBLANK ( [Selected Measure Value] ), 1, 0 )
                    )
        //Count up the number of sales with data for each manager
        VAR salesWithDataFormanager = SUMX ( x, [Flag] )
 
        //If the number of managers for the facility matches the number of 
        //selected managers, include that value, otherwise exclude it.
        RETURN
            IF ( salesWithDataFormanager = SelectedmanagerCount, [Selected Measure Value] )
        )
    RETURN output

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    hello ifarias  can you please provide some sample data

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      still not sure but try :

       

      Selected Measure Value = 
    SWITCH(SELECTEDVALUE('Measures for Slicer'[Measure])
        , "Managers", [Manager Names]
        , BLANK()
        )

Selected Measure Value (only for managers) = 
    // Get the number of managers you selected
    VAR SelectedmanagerCount = CALCULATE(COUNTROWS(VALUES(table[managers]))
                                        , ALLSELECTED(table))
    VAR output = SUMX (
        VALUES( table[manager] ),    //Get each manager in the current context
        VAR x = ADDCOLUMNS (            //For each manager, set a flag for whether 
                                        //there is data for each selected manager
                    ALLSELECTED ( table ),
                    "Flag", IF ( NOT (ISBLANK ( [Selected Measure Value] ), 1, 0 )
                    )
        //Count up the number of sales with data for each manager
        VAR salesWithDataFormanager = SUMX ( x, [Flag] )
 
        //If the number of managers for the facility matches the number of 
        //selected managers, include that value, otherwise exclude it.
        RETURN
            IF ( salesWithDataFormanager = SelectedmanagerCount, [Selected Measure Value] )
        )
    RETURN output

       

    • ifarias's avatar
      ifarias
      New Member
      3 years ago

      Hello! Thanks for the suggestion, but doing that only returns the *value "OR" value* results, which is what I am trying to avoid.