Forum Discussion

RMDNA's avatar
RMDNA
Solution 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'[V...
Need Help
  • Sean's avatar
    Sean
    9 years ago
    Measure =
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( 'TABLE'[Value] ),
        FILTER ( 'TABLE', 'TABLE'[VALUE] <> BLANK () )
    ),
    DISTINCTCOUNT ( 'TABLE'[VALUE] ),
    0
)
Sean's avatar
Sean
Community Champion
9 years ago

How about this...

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

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