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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
littlemojopuppy
Community Champion
Community Champion

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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

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

Anonymous
Not applicable

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

BOOM!  Thank you!!!

 

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

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!

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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