Forum Discussion

RMDNA's avatar
RMDNA
Icon for Solution Sage rankSolution Sage
9 years ago
Solved

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

Need Help
  • Sean's avatar
    Sean
    9 years ago
    Measure =
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( 'TABLE'[Value] ),
        FILTER ( 'TABLE', 'TABLE'[VALUE] <> BLANK () )
    ),
    DISTINCTCOUNT ( 'TABLE'[VALUE] ),
    0
)

5 Replies

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    9 years ago

    How about this...

    Measure =
CALCULATE (
    DISTINCTCOUNT ( 'TABLE'[Value] ),
    FILTER ( 'TABLE', 'TABLE'[VALUE] <> BLANK () )
)
    • RMDNA's avatar
      RMDNA
      Icon for Solution Sage rankSolution Sage
      9 years ago

      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's avatar
        Sean
        Icon for Community Champion rankCommunity Champion
        9 years ago
        Measure =
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( 'TABLE'[Value] ),
        FILTER ( 'TABLE', 'TABLE'[VALUE] <> BLANK () )
    ),
    DISTINCTCOUNT ( 'TABLE'[VALUE] ),
    0
)