Hide zero values from a Matrix using a Slicer instead of a Visual Level Filter

[imranamikhan]

Hi everyone,

I am using Visual Level filters to hide zero values from a column subtotal in a Matrix. Is there a way to do this using a slicer instead?

More context if required:

I have three measures:

1. The first measure is summing a column based on a condition: 

   EDWF. = CALCULATE(SUM('FPI & Accounts Fusion & EDWF Data'[POSTED VALUE]),'FPI & Accounts Fusion & EDWF Data'[Data Source]="EDWF"))

2. The second measure is summing a column based on a different condition

   Fusion. = CALCULATE(SUM('FPI & Accounts Fusion & EDWF Data'[POSTED VALUE]),'FPI & Accounts Fusion & EDWF Data'[Data Source]="Fusion"))

3. The third measure is a simple variance calculation - it subtracts the first measure from the second: 

   VAR. = ROUND([EDWF]-[Fusion],2)

I would like to create a slicer which will allow a user to toggle between displaying all data or data without zeroes based on the third measure (VAR).

[jdbuchanan71 · Accepted solution]

Hello @imranamikhan 

We will need to do a couple steps to get the result you are looking for.

1. Create a table with the selection you want the users to have.

2. Write a measure to read the users selection and do the calc on what rows to show.  The default is "Show Zeros" in case the user makes no selection or selects both choices.

Zero Row Filter = 
VAR ZeroChoice = SELECTEDVALUE ( 'zeros table'[zero selection], "Show zeros")
RETURN IF ( ZeroChoice = "Hide zeros" && [VAR] = 0, 0, 1)

3. Add that measure to the visual filter and set to = is 1

4. Add a slicer with your selection column from the table in step 1 to the page.

[SpyrosD]

Awesome solution. Thank you very much!!!

[Anonymous]

Fantastic, this helped and worked for me!

[imranamikhan]

Hi again - your solution worked perfectly. Thank you so much for your support. You truly are an asset to this community.