Forum Discussion
To select multiple values from slicer : customised
Hi,
I have a table with attributes of customer. I have a customised slicer to get the count of rows where the value is > 0:-
I have used the column name :- no. of prod1 only,no. of prod2 only, no. of prod 1 and 2, no. of other as slicer. (I don't want to unpivot the table)
TableA-
| ID | no. of prod1 only | no. of prod2 only | no. of prod 1 and 2 | no. of other | Online |
| 1 | 1 | 0 | 0 | 0 | Y |
| 2 | 1 | 0 | 0 | 2 | Y |
| 3 | 1 | 0 | 0 | 0 | Y |
| 4 | 1 | 1 | 1 | 0 | N |
| 5 | 1 | 0 | 0 | 0 | N |
| 6 | 1 | 0 | 0 | 0 | Y |
| 7 | 1 | 2 | 0 | 5 | Y |
| 8 | 1 | 0 | 0 | 0 | N |
| 9 | 1 | 0 | 0 | 0 | Y |
I have made a table for slicer and there is no relationship between the tables:-
Custom_slicer
Slicer
| no. of prod1 only |
| no. of prod2 only |
| no. of prod 1 and 2 |
| no. of other |
Dynamic column =
var _selectedfunction=SELECTEDVALUE(Custom_slicer[Slicer])
return
SWITCH(
_selectedfunction,
"no. of prod1 only", COUNTROWS(CALCULATETABLE(TableA,TableA[no. of prod1 only] >0)),
"no. of prod2 only", COUNTROWS(CALCULATETABLE(TableA,TableA[no. of prod2 only] >0)) ,
"no. of prod 1 and 2", COUNTROWS(CALCULATETABLE(TableA,TableA[no. of prod 1 and 2] >0)),
"no. of other", COUNTROWS(CALCULATETABLE(TableA,TableA[no. of other] >0)),
BLANK()
)
Currently, it is able to display the count if only one value is selected in the slicer. But when I try to select multiple values it is giving blank. I know it is because of SELECTEDVALUE. I searched through the forum but didn't get similar problem. Any idea how to get this to work?
Any help would much appreciated.
Anonymous
You can try this way:Dynamic column = var _selectedfunction=VALUES(Custom_slicer[Slicer]) return if( "no. of prod1 only" in _selectedfunction , COUNTROWS(CALCULATETABLE(TableA,TableA[no. of prod1 only] >0)))+ if( "no. of prod2 only" in _selectedfunction , COUNTROWS(CALCULATETABLE(TableA,TableA[no. of prod2 only] >0))) + if( "no. of prod 1 and 2" in _selectedfunction , COUNTROWS(CALCULATETABLE(TableA,TableA[no. of prod 1 and 2] >0)))+ IF( "no. of other" in _selectedfunction , COUNTROWS(CALCULATETABLE(TableA,TableA[no. of other] >0)))
1 Reply
- Fowmy
Super User
Anonymous
You can try this way:Dynamic column = var _selectedfunction=VALUES(Custom_slicer[Slicer]) return if( "no. of prod1 only" in _selectedfunction , COUNTROWS(CALCULATETABLE(TableA,TableA[no. of prod1 only] >0)))+ if( "no. of prod2 only" in _selectedfunction , COUNTROWS(CALCULATETABLE(TableA,TableA[no. of prod2 only] >0))) + if( "no. of prod 1 and 2" in _selectedfunction , COUNTROWS(CALCULATETABLE(TableA,TableA[no. of prod 1 and 2] >0)))+ IF( "no. of other" in _selectedfunction , COUNTROWS(CALCULATETABLE(TableA,TableA[no. of other] >0)))