Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.