Filter report values that are >= 1

Questions & Answers
1

I have a report for the number of days in current status and prefer to only show if the value was greater than 0 with the field format set to integer but would show values of 0 because the actual value was 0.16. I want to be able to show values that are greater than or equal to 1 for the report

39%20PM
Screen Shot 2019-02-19 at 1.50.39 PM.png1451×609 121 KB

2

Hey, how about using conditional formatting like that, whith both text and background white:

image
image.png1018×158 6.88 KB

I use it often in Excel to hide things. :slight_smile:

3

That does work in that the value is not shown as the row is totally white. However, the ask is to not show any values less than 1 in the solutions I end up with an issue value but the column is white across all values.

55%20AM
Screen Shot 2019-02-22 at 11.01.55 AM.png1297×740 136 KB

4

Hi Brian,

You can left-click on the “Days In Current Status” column and select “Filter Rows”. Then choose the > symbol and type in 1.

That way, any row that has a value less than 1, such as your 0.16 example, will be removed from the report.

Hope this helps.

– Malik Graves-Pryor

5

Malik -

I have tried that method but the issue is with that solution is if you filter on Days in Current Status you end up with only one column that you filter on - the rest of the columns are removed.

23%20AM
Screen Shot 2019-02-25 at 10.09.23 AM.png1120×442 75.2 KB

6

Oh I see. You can apply the same filter to all of the other five columns that have Days in Current Status as well.

That should work I believe.

Give that whirl?

– Malik Graves-Pryor

7

No as soon as you filter on one all the rest are hidden so you can not filter on more than one

8

What is the MDX for that “Days in current status” measure?

– Malik Graves-Pryor

9

–DateDiffDays([Measures].[Issue status updated date], Now())

Sum(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
[Measures].[Issues due] > 0 ),
DateDiffDays([Measures].[Issue status updated date], Now())
)

10

Ok, you could try using CASE in another calculated measure

CASE WHEN [Measures].[Days in current status] >= 1
THEN "Good"
END

Then filter on that calculated measure’s column. When calculated measure = Good is the filter, and that would get rid of any rows where that isn’t the case.

This also depends on what dimensions you have in columns. It may not work if you have [Measures] and [Status] in dimensions as the Days in current status measure would show up in the column you’re trying to filter on.

Let me know.

– Malik Graves-Pryor

11

That does not work either it is the same scenario as before you end up with only one column and yes Status and the measure are in the Column section.

12

Took it one extra step. Seemed to work in the cobbled together report I created based on your specs.

Replace your existing [Days in current status] measure with the following:

CASE WHEN 
Sum(
	Filter(
		Descendants([Issue].CurrentMember, [Issue].[Issue]),
		[Measures].[Issues due] > 0 ),
		DateDiffDays(
			[Measures].[Issue status updated date], 
			Now()
		)
	) >= 1
THEN
	Sum(
	Filter(
		Descendants([Issue].CurrentMember, [Issue].[Issue]),
		[Measures].[Issues due] > 0 ),
		DateDiffDays(
			[Measures].[Issue status updated date], 
			Now()
		)
	)
END
13

Malik;

That was it - perfect thank you very much

14

Happy to help :slight_smile:

– Malik Graves-Pryor

15

Malik -

I placed a ticket with EazyBI and they came back with an additional option so wanted to share

Sum(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
[Measures].[Issues due] > 0
AND
Cache(DateDiffDays([Measures].[Issue status updated date], Now())) >= 1
),
DateDiffDays([Measures].[Issue status updated date], Now())
)

16

Cheers!

– Malik Graves-Pryor

17

Hi @briantaylor,

Thank you for sharing the solution from the support ticket with the community. It is a pity I didn’t see this post sooner :wink:

Kind regards,
Roberts // eazyBI support