Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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 )
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.
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 )
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.
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.
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)])
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
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.
When you attempted the previously propsed measure, you are currently using a slicer on 2019 right?
Yes, I have 4 x slicers to allow me to select the year, the month, the project and/or the resource.
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.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |