cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Post Prodigy

Uncheck select all option in slicer before making another selection

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"
)
)``````

1 ACCEPTED SOLUTION
Frequent Visitor
Hi Dokat,
Please find the code below. If it solves your issue, kindly accept it as a solution! Thanks.
Special Slicer TY =
IF (
ISFILTERED ( 'POS CY'[Slicer] ),
CALCULATE ( [POS TY], 'POS CY'[Slicer] <> "Maintenance" ),
CALCULATE ( [POS TY], 'POS CY'[Slicer] <> "Maintenance" )
)
7 REPLIES 7
Frequent Visitor
Hi Dokat,
Please find the code below. If it solves your issue, kindly accept it as a solution! Thanks.
Special Slicer TY =
IF (
ISFILTERED ( 'POS CY'[Slicer] ),
CALCULATE ( [POS TY], 'POS CY'[Slicer] <> "Maintenance" ),
CALCULATE ( [POS TY], 'POS CY'[Slicer] <> "Maintenance" )
)
Post Prodigy

@tej147 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

Post Prodigy

@tej147 Sorry for delay in my response. This worked perfectly. Thank you!

Frequent Visitor

Hi dokat,

You can disable "Select All" option of slicer in format pane.

Post Prodigy

@tej147 Thanks. I need to give user an option to select all.

Super User

In this formula you need to try
IF (
ISFILTERED ( 'POS CY'[Slicer]),
[POS TY],
CALCULATE (
[POS TY],
selectedvalue('POS CY'[Slicer]) <> "Maintenance"
)
)

MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!

Post Prodigy

@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.

Announcements

Fabric certifications survey

Certification feedback opportunity for the community.

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors