In excel with a Pivot table, don't show rows with no data

user_3634056 0
Viewed 76

I've got a pivot table of employees and the hours and number of shifts they worked in a month. I made a calculated measure that shows Yes if the total hours is above 160, no if it's below 160, and blank if it's below 1.

The measure looks like this: IIF([Measures].[Hours]<1,"",IIF([Measures].[Hours]>=160,"Yes","No"))

I have it filtered to only show the month of August, and it only shows employees who worked in August.

Once I add the calculated measure to the table, it shows all employees that every worked in any month, with blank values for the number of hours.

How do I get it to only show employees who worked at least one hour? There's no filter available above the hours column, and when I try to drag it to the filter box it says, "The field you are moving cannot be moved to that area of the report".

mdx pivot-table excel
1 Answers

Empty string ("") is not the same as NULL.

Return NULL for your first IIF true condition and see whether it gives you the desired output

IIF
(
  [Measures].[Hours] < 1
 ,NULL
 ,IIF
  (
    [Measures].[Hours] >= 160
   ,"Yes"
   ,"No"
  )
)
user_16892260
Related