Forum Discussion
ifarias
3 years agoNew Member
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 AN...
- Anonymous3 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
AliakseiIvaneka
3 years agoAdvocate I
Hi, you can have multiple selection by pressing CTRL and selecting other values
- Anonymous3 years agoNot applicable
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 - ifarias3 years agoNew Member
Hello! Thanks for the suggestion, but doing that only returns the *value "OR" value* results, which is what I am trying to avoid.