cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Solution Sage
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
Community Champion

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

View solution in original post

4 REPLIES 4
Community Champion
Community Champion

How about this...

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

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
Community Champion

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

View solution in original post

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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors