Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Measure = DIVIDE ( CALCULATE ( DISTINCTCOUNT ( 'TABLE'[Value] ), FILTER ( 'TABLE', 'TABLE'[VALUE] <> BLANK () ) ), DISTINCTCOUNT ( 'TABLE'[VALUE] ), 0 )
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."
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |