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
Anonymous
3 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