Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@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)))
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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)))
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |