Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
dokat
Post Prodigy
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
tej147
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" )
)

View solution in original post

7 REPLIES 7
tej147
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" )
)

@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

 

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

tej147
Frequent Visitor

Hi dokat,

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

selectall.png

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

VijayP
Super User
Super User

@dokat 

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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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