CALCULATE() not filtering correctly?

[CamronBute]

Consider the following query result (in the Edit Queries screen):

Now here are two measures I have made on that query:

This is the result:

I expected to see:

Am I missing something, or is this a bug?

[tringuyenminh92 · Accepted solution]

Hi @CamronBute,

To use these method or multiple conditions in filtering, please wrap them in Filter method

CALCULATE([Total Views],FILTER(Views,NOT(ISBLANK(Views[Who]))))

[OwenAuger · Accepted solution]

Hi @CamronBute

Two reasons I believe are contributing to your unexpected results:

1. The empty values in the Who column are empty strings rather than null values (empty strings and nulls look the same in Data view, but are distinguishable in the Query Editor).
2. CALCULATE filter arguments overwrite the filter context (by default) rather than intersecting with it.

Assuming the blank names are empty strings, this code should work:

Total authenticated views =
CALCULATE (
    [Total Views],
    Views[Who] <> "",
    VALUES ( Views[Who] )
)

[CamronBute]

Yes, I see now. Thank you for explaining the behaviour I was missing, too.

[tringuyenminh92 · Accepted solution]

Hi @CamronBute,

To use these method or multiple conditions in filtering, please wrap them in Filter method

CALCULATE([Total Views],FILTER(Views,NOT(ISBLANK(Views[Who]))))

[CamronBute]

Yes, this works. Thank you!