cancel
Showing results for
Did you mean:
Solution Sage

## Measure: FILTER([value] is not blank

I'm trying to create a measure where I can reference a pre-filtered value. It will end up being a %, but for simplicity:

Measure = CALCULATE(DISTINCTCOUNT('TABLE'[Value]),FILTER('TABLE','TABLE'[VALUE] (is not blank)

I just need a count of the value when it is not blank/without nulls. I've tried:

TABLE [VALUE] =ISBLANK(FALSE), =ISEMPTY(FALSE), = <> BLANK(), etc.

You guys have been a great help before. Help me again? Thanks

1 ACCEPTED SOLUTION
Community Champion
```Measure =
DIVIDE (
CALCULATE (
DISTINCTCOUNT ( 'TABLE'[Value] ),
FILTER ( 'TABLE', 'TABLE'[VALUE] <> BLANK () )
),
DISTINCTCOUNT ( 'TABLE'[VALUE] ),
0
)```
4 REPLIES 4
Community Champion

```Measure =
CALCULATE (
DISTINCTCOUNT ( 'TABLE'[Value] ),
FILTER ( 'TABLE', 'TABLE'[VALUE] <> BLANK () )
)```
Solution Sage

That gives the correct value. When I try to make it a %, however,

Measure =
CALCULATE (DISTINCTCOUNT ( 'TABLE'[Value] ), FILTER ( 'TABLE', 'TABLE'[VALUE] <> BLANK () ) )

/ DISTINCTCOUNT('TABLE'[VALUE])),

(i.e. dividing the filtered value by its unfiltered self), it gives me

"A function FILTER has been used in a True/False expression that is used as a table filter expression. This is not allowed."

Community Champion
```Measure =
DIVIDE (
CALCULATE (
DISTINCTCOUNT ( 'TABLE'[Value] ),
FILTER ( 'TABLE', 'TABLE'[VALUE] <> BLANK () )
),
DISTINCTCOUNT ( 'TABLE'[VALUE] ),
0
)```
Solution Sage

That did it. Thanks for making the formatting clear - it was easy to follow the logic.

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!