Forum Discussion

littlemojopuppy's avatar
littlemojopuppy
Community Champion
7 years ago
Solved

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. ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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()
        )
    )

littlemojopuppy's avatar
littlemojopuppy
Community Champion
7 years ago

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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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()
        )
    )

  • littlemojopuppy's avatar
    littlemojopuppy
    Community Champion
    7 years ago

    BOOM!  Thank you!!!

     

    I feel kind of dumb for not checking those...  :smileyfrustrated:

    • littlemojopuppy's avatar
      littlemojopuppy
      Community Champion
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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.