Calculated measure to select only 'non-empty' values

Questions & Answers
1

This seems so simple yet I cannot figure it out.

If I add a field to my Page Filter list, I can easily set the dropdown to select only records where the field is empty; I can search and bookmark for (none).

However, I would like the opposite scenario. I want the dropdown to allow selection of only ‘non-empty’ records. I can create a calculated member but I cannot get the syntax correct. Do I use NOT IsEmpty()? This is just a freeform text field but I want to exclude records where the field is NOT populated.

2

Tried to use the page filter (have to select the wanted field first from your measures)? → filter rows → not matches (none)

3

This is not providing the desired results. Records with “(none)” are still being included. There has to be a syntax / formula that can be used in a Calculated Measure to only retrieve “non-empty” records??

Ultimately I am trying to get a view of Total Issues Created / Resolved each month, but only want issues where the “Service Ticket” field has values in it (non-empty) included in the total created/resolved calculations.

This current view includes all issues and not the desired filter. For instance, November has 74 Issues Created; I want to exclude out of the 74 any issues where Service Ticket is (none).

image
image1085×359 10.4 KB

4

Try this, make the same calculated for issues resolved then.

Count(
Filter(
–iterate through linked members
Descendants([Issue].CurrentMember, [Issue].[Issue]),
NOT ([Service Tickets].[Issue created]) MATCHES “(none)”
)
)

5

Thanks for the suggestion. I have not tried it as I found an alternate approach. I created a new calculated field at Import which is populated with “Y” if the Service Ticket field is populated. I then use this new field to filter in the records I want to include in the report. This is working well.