cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Slicers Not Filtering?

Hi!  I have a question about something weird I'm hoping someone could give me some insight in why...

 

I have a table populated with customer names along with some financial info and nine slicers.  Seven of those slicers (the ones populated from tables along dimensions) are not actually filtering the table.  The table still lists all customers and just doesn't present the financial data for whatever isn't selected by the slicer.  The two slicers that are filtering the table are populated from customer categories found in the fact table itself.

Annotation 2019-05-14 130149.png

It's a simple data model...one fact table and three dimensions.  All relationships are 1:M and single direction cross-filtering.

Annotation 2019-05-14 130254.png

I've created other reports where slicers filter tables appropriately - meaning the only data shown is what is selected by the slicer.  I've looked around on here and Google in general and haven't seen anything like this.  Any insight anyone can provide would be appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Slicers Not Filtering?

In some of the measures you are checking to see if should have a value, just need to change that from 0 to BLANK()

 

Contribution Margin Per Case = 
    IF(
        SUM('Raw Data'[Volume]) = blank(),
        BLANK(),
        DIVIDE(
            SUM('Raw Data'[Contribution Margin]),
            SUM('Raw Data'[Volume]),
            BLANK()
        )
    )

Net TGP Per Case = 
    IF(
        SUM('Raw Data'[Volume]) <= 0,
        BLANK(),
        DIVIDE(
            SUM('Raw Data'[Net TGP]),
            SUM('Raw Data'[Volume]),
            BLANK()
        )
    )

Contribution Margin Rate = 
    IF(
        SUM('Raw Data'[Net Sales]) <= 0,
        BLANK(),
        DIVIDE(
            SUM('Raw Data'[Contribution Margin]),
            SUM('Raw Data'[Net Sales]),
            BLANK()
        )
    )

Blank instead of 0.png

View solution in original post

6 REPLIES 6
Super User
Super User

Re: Slicers Not Filtering?

Any chance you can upload some sample data? Onedrive work well

Re: Slicers Not Filtering?

Hi!  You should be able to download the PBIX file from here

Super User
Super User

Re: Slicers Not Filtering?

In some of the measures you are checking to see if should have a value, just need to change that from 0 to BLANK()

 

Contribution Margin Per Case = 
    IF(
        SUM('Raw Data'[Volume]) = blank(),
        BLANK(),
        DIVIDE(
            SUM('Raw Data'[Contribution Margin]),
            SUM('Raw Data'[Volume]),
            BLANK()
        )
    )

Net TGP Per Case = 
    IF(
        SUM('Raw Data'[Volume]) <= 0,
        BLANK(),
        DIVIDE(
            SUM('Raw Data'[Net TGP]),
            SUM('Raw Data'[Volume]),
            BLANK()
        )
    )

Contribution Margin Rate = 
    IF(
        SUM('Raw Data'[Net Sales]) <= 0,
        BLANK(),
        DIVIDE(
            SUM('Raw Data'[Contribution Margin]),
            SUM('Raw Data'[Net Sales]),
            BLANK()
        )
    )

Blank instead of 0.png

View solution in original post

Re: Slicers Not Filtering?

BOOM!  Thank you!!!

 

I feel kind of dumb for not checking those...  Smiley Frustrated

Re: Slicers Not Filtering?

So...I was reluctant to ask earlier why was everything still showing up despite the slicer filtering.  But I didn't want to seem even more dumb.  After thinking about it, I'm going to answer my own unasked question so I feel slightly less dumb: it's because the measure was actually producing a value (maybe not necessarily a valid value, but still a value).  And because there was a value it was going to show up.

Thanks again for your help!

Highlighted
Super User
Super User

Re: Slicers Not Filtering?

Not a dumb question at all, and you are correct. Since your formula had logic to compute a zero, that's what the dax did. Which is why those rows didnt disappear when you filtered on slicers. As far as dax was concerned, it was always producing a value, either 0 or the actual figure. By default blank values will not be shown ( you can turn those on if you want though). So when you tell dax to give a blank() value, you are really telling dax not to show that value. 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)