Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hi,
I have a data table (POS CY) and slicer ([Slicer]) with 4 selection choices and select all option. I have below code if user clicks select all option in the slicer it excludes "Maintenance" sales from total in the data table. However i noticed after clicking select all if the user clicks on another slicer option formula doesnt work and totals include "Maintenance". Basically it bypasses formula. One way to avoid this is to force user to uncheck'select all" option. Is this possible to do? If not is there a way to modify the formula where it excludes "Maintenance" sales unless user specifically clicks and selects?
Below you will find the slicer selections
"Grocery"
'Drug"
"Depot"
"Maintenance
Special Slicer TY =
IF (
ISFILTERED ( 'POS CY'[Slicer]),
[POS TY],
CALCULATE (
[POS TY],
'POS CY'[Slicer] <> "Maintenance"
)
)
Solved! Go to Solution.
@Anonymous I have a quetsion when i "select all" or uncheck some other slicer selection it works great. However if i dont "select all" and individually select it doesnt work. Is there a work around this? Thanks
@Anonymous Sorry for delay in my response. This worked perfectly. Thank you!
Hi dokat,
You can disable "Select All" option of slicer in format pane.
@Anonymous Thanks. I need to give user an option to select all.
In this formula you need to try
IF (
ISFILTERED ( 'POS CY'[Slicer]),
[POS TY],
CALCULATE (
[POS TY],
selectedvalue('POS CY'[Slicer]) <> "Maintenance"
)
)
Proud to be a Super User!
@VijayP Thank you for your response. However when i run the code i am receving below error message. Appreciate any help. Thanks
The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.
User | Count |
---|---|
84 | |
77 | |
64 | |
51 | |
46 |
User | Count |
---|---|
101 | |
43 | |
39 | |
39 | |
36 |