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
ATREZISE
Helper I
Helper I

Display Blank as Zero

Hi All 

 

There appears to be lots of posts about this with the simplest way just to add 0 to a measure...

 

i.e. measure = SUM(totals[revenue]) + 0

 

However adding 0 seems to discard any filters on the underlying tables so from having a nice matrix which only shows data where there's some to show, it opens up all rows which makes the matrix more cluttered with data I don't wish to see.

 

For example:

 

Using the measure without 0 shows the data as below...

 

EMP     PROJECT    MONTH1    MONTH2    MONTH3

emp1   project1     1000.00       1500.00      1200.00

emp1   project2     2000.00

emp2   project1                         1300.00      1400.00

 

Using the measure with 0 shows the data as below...

 

EMP     PROJECT    MONTH1    MONTH2    MONTH3

emp1   project1     1000.00       1500.00      1200.00

emp1   project2     2000.00

emp2   project1                         1300.00      1400.00

 

Using the measure without 0 shows the data as below...

 

EMP     PROJECT    MONTH1    MONTH2    MONTH3

emp1   project1     1000.00       1500.00      1200.00

emp1   project2     2000.00

emp2   project3                         1300.00      1400.00

 

Using the measure after adding 0 shows the data like this...

 

EMP     PROJECT    MONTH1    MONTH2    MONTH3

emp1   project1     1000.00       1500.00      1200.00

emp1   project2     2000.00             0.00            0.00

emp1   project3           0.00             0.00            0.00

emp2   project1           0.00             0.00            0.00

emp2   project2           0.00             0.00            0.00

emp2   project3           0.00       1300.00      1400.00

 

So the formatiing is correct but I now see 3 x additional rows that I don't need to see as they have no relevant data. The promblem is amplified as we have hundreds of emplyees and project combinations.

 

Is there any way to overcome this?

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @ATREZISE 

Create a measure

Measure 4 =
IF (
    CALCULATE (
        SUM ( 'Table 4'[value] ),
        ALLEXCEPT ( 'Table 4', 'Table 4'[emp], 'Table 4'[project] )
    )
        = BLANK (),
    BLANK (),
    SUM ( 'Table 4'[value] ) + 0
)

Capture20.JPG

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

Hi @ATREZISE 

 

If one of the solutions worked would you then mark it as an accepted solution? Smiley Happy

v-juanli-msft
Community Support
Community Support

Hi @ATREZISE 

Create a measure

Measure 4 =
IF (
    CALCULATE (
        SUM ( 'Table 4'[value] ),
        ALLEXCEPT ( 'Table 4', 'Table 4'[emp], 'Table 4'[project] )
    )
        = BLANK (),
    BLANK (),
    SUM ( 'Table 4'[value] ) + 0
)

Capture20.JPG

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Maybe you can validate based on the sum of the measure YTD or any other datefilter (I'm assuming you are looking at a specific period of months).

 

I.e.

 

measure YtD = TOTALYTD( SUM(totals[revenue]), Date[Date])

measure = IF( ISBLANK([measure YtD]), BLANK(), SUM(totals[revenue]) + 0)

se

Hi

 

Thanks for the suggestion but I get the same result - the filter is not recognised and all rows are returned.

 

 

Anonymous
Not applicable

I have tried to recreate your model (or a subset of it). I believe that it can work with the following measures:

 

revenue (+ 0) = SUM( fact[Revenue]) + 0
revenue = IF( CALCULATE( [revenue (+ 0)]; ALL( 'Date')) = 0; BLANK(); [revenue (+ 0)])

test.PNG
test2.PNG

This way I am able to only show the employees who have an actual value for any project (as in the bottom picture)

Hi 

Thanks again for looking at this. I've tried to apply your suggestion but I still get the same problem.

I created the two measures - the first just to sum revenue and add 0, the second to do a conditional check.

Here are my results:

This is as per your instructions - with 0 

revenue (+ 0) = SUM( fact[Revenue]) + 0

111.png

if I remove the 0 from the first measure...

revenue (+ 0) = SUM( fact[Revenue])

the data collapses back down as I wish to see it - but with blanks.

22222.png

tex628
Community Champion
Community Champion

When you attempted the previously propsed measure, you are currently using a slicer on 2019 right?


Connect on LinkedIn

Yes, I have 4 x slicers to allow me to select the year, the month, the project and/or the resource.

Anonymous
Not applicable

I have tried something a bit similar to the previous post, and I believe it works in my PBI report.

 

total sum (validation) = 
    CALCULATE( [revenue (+0)] + [forecast (+0)]; ALLSELECTED( 'date'[Month]; 'date'[Year]))

revenue = 
    IF( [total sum (validation)] = 0; BLANK(); [revenue (+0)])

forecast = 
    IF( [total sum (validation)] = 0; BLANK(); [forecast (+0)])

Then you should be able to keep the filtercontext from your slicers.

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.