Countrows and Filter giving unexpected results

[marktait]

Hi.

I have a DAX measure, which looks for the maximum date in a DateColumn:

Defender - Current Month = MAX(Defender[ExtractDate])

The ExtractDate is formatted as a Date:

I want to count the number of rows where the [CVSS v3]score is 9 or above AND the [ExtractDate] equals the measure [Defender - Current Month].

CVSS 9-10 current month = COUNTROWS(

FILTER(

Defender,

Defender[CVSS v3]>=9 &&

Defender[ExtractDate]=[Defender - Current Month]

))

The result should be 97, however it is 894 (which is exactly how many rows have CVSS v3 >=9).

Can anyone see why my Filter is not also filtering on "Defender[ExtractDate]=[Defender - Current Month]"?

Thanks for any help,

Mark

[marktait]

Hi Owen.

Both of those worked for me.

Thank you very much for going to the effort of explaining why they work too - I really appreciate that.

All the best, Mark

[OwenAuger · Accepted solution]

Hi @marktait 

The reason for this behaviour is that when a measure is evaluated in a row context, that row context is first converted into an equivalent filter context.

This is known as context transition (see this article).

In your example, [Defender - Current Month] is being evaluated in the row context of Defender created by FILTER (an iterator).

For each row where it is evaluated

• That row's value of Defender[ExtractDate] (and the values of the other columns) becomes a filter.
• When [Defender - Current Month] is evaluated with this filter, there is a single value of Defender[ExtractDate] visible, which is therefore also the maximum.
• So the condition Defender[ExtractDate]=[Defender - Current Month] is always true.

To fix this, you can use a couple of methods:

1. Evaluate [Defender - Current Month] in a variable outside FILTER:

CVSS 9-10 current month fix v1 =
VAR CurrentMonth = [Defender - Current Month]
RETURN
    COUNTROWS (
        FILTER (
            Defender,
            Defender[CvSS v3] >= 9 && Defender[ExtractDate] = CurrentMonth
        )
    )

2. Use LASTDATE to make the measure more concise (my preferred method):

CVSS 9-10 current month fix v2 =
CALCULATE (
    COUNTROWS ( Defender ),
    LASTDATE ( Defender[ExtractDate] ),
    Defender[CvSS v3] >= 9
)

A general principle is that it is better to filter specific columns rather than tables, which is another reason I would prefer the 2nd version.

Does this work for you?

Regards