Filter out blanks

[Jriker1]

I have a chart that displays information per quarter. I have a field called quarter-year that says Q1 or Q2 or Q3 or Q4. Problem is some data has no dates so the calculation to populate Q1... doesn't always trigger. so in the report, on the horizontal bar it consists of blank, then Q1 Q2 Q3 Q4. I have eliminated the blank data by adding a filter on the graph and on the field buttons check "Select Multiple Values", and then uncheck the blank box. Problem is when there is new data, valid data, those come in unchecked and I have to update the filter manually checking them each time. Is there a way on a report to eliminate blank values thru a measure or something?

Thanks.

JR

 

[miguel.escobar]

in your measure, write this IF( [what you're calculating] = blank(), blank(), [what you're calculating])

 

[Jriker1]

Thanks for the suggestion. New to Powerpivot so banging my head. Tried a bunch of methods but the results were odd and neve eliminated blanks. Essentially I'm setup this way currently which is visually nice:

Graph with no blanks in the beginning:

Here is how my Pivot Field List is setup:

For the above Sum of Device Count it's setup Selected "Sum" and : =SUM('Table_country'[Device Count])


The way I'm currently filtering out blanks is:

Any new dates shows in that list unchecked and I have to check it. Trying to figure out how to replicate this functionality. showing the growth over time. Below is a screen sot of a sample of my data:

As you can see some countries have no dates and they show up before 2012Q1 if I don't manually uncheck the blank data. There are also cells that have the 2012Q1, 2012Q2, etc in them which are also blank if there is no date, but those are calculated fields in PowerPivot and couldn't show those well. If someone can let me know exactly how to do this "right" would be appreciated. Trying to learn yet want to show the power of this tool to my management so we can get some official training.

Thanks.

JR

 

[andero09]

The way I'm currently filtering out blanks is

 

[Jriker1]

The way I'm currently filtering out blanks is

I think your message was cut off or the graphic was missing. Saw a partial sentence and a red X before your text.

THanks.

 

[MD610]

Try wrapping your measure in something like this:
New Measure:=IF(HASONEVALUE('Table1'[Quarter-Year]),IF(VALUES('Table1'[Quarter-Year])=BLANK(), BLANK(), [Sum of Device Count]), BLANK())

Use this new measure in your pivot/chart instead of Sum of Device Count.

 

[Jriker1]

Thanks man. That did the ticket. Helps me understand how to do things also.