cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
RMDNA
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
Sean
Community Champion
Community Champion

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

View solution in original post

5 REPLIES 5
Sean
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."

 

 

Sean
Community Champion
Community Champion

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

Hello,

I have a little question: why is the Filter function applied while the Calculate function itself has a filter?

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

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors