topic Suddenly Can't Filter in Measures in DAX Commands and Tips

Suddenly Can't Filter in Measures

rusgesig — Wed, 30 Mar 2022 07:58:42 GMT

All of a sudden the filtered value in measures don't work.

I made a fresh dataset with one table to test it. The result is the same whether I use a quick measure or not. I have been using this format for over 2 years, and this is the first time this happened.

Re: Suddenly Can't Filter in Measures

tamerj1 — Wed, 30 Mar 2022 08:11:44 GMT

Hi rusgesig

please try

Count of id for Active =
CALCULATE ( DISTINCTCOUNT ( table[id] ), "Active" IN table[status] )

Re: Suddenly Can't Filter in Measures

rusgesig — Wed, 30 Mar 2022 08:33:01 GMT

This works, still really weird that the previous format stopped working - am I going to have go through old datasets to amend the measures.

active =

CALCULATE(

COUNTA('table'[date_active])

,FILTER('table','table'[status] = "Active")

,USERELATIONSHIP(_calendar[reldate],'table'[date_active]))

Re: Suddenly Can't Filter in Measures

rusgesig — Wed, 30 Mar 2022 08:46:54 GMT

A function 'CONTAINSROW' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Re: Suddenly Can't Filter in Measures

tamerj1 — Wed, 30 Mar 2022 08:56:18 GMT

rusgesig

Count of id for Active = CALCULATE ( DISTINCTCOUNT ( table[id] ), FILTER ( table, "Active" IN table[status] ) )

Re: Suddenly Can't Filter in Measures

rusgesig — Wed, 30 Mar 2022 09:12:52 GMT

Yes, I said that works in my second post - my question is why the previous format stopped working all of a sudden?

Re: Suddenly Can't Filter in Measures

v-janeyg-msft — Mon, 04 Apr 2022 07:43:13 GMT

Hi, rusgesig

I think this is a syntax error and not correct.

And I don't think there's anything wrong with your first writing, But generally if there is only one choice, we can directly use 'table'[status] = "Active"' as you mentioned later.

I suspect that your matrix table has columns from other tables affecting the results. Can you add more information? What's the difference between your desired result and the current result?

Best Regards,
Community Support Team _ Janey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Suddenly Can't Filter in Measures

rusgesig — Tue, 05 Apr 2022 07:40:03 GMT

Hi,

I brought this table into a new dataset for testing purposes. This is the only table in the dataset.
The desired result for the measure should be the same as 'Count of id' where the status in rows is 'Active'.

Like I explained before, I've been using this syntax for over 2 years and all of a sudden it's showing the same result for all statuses, when the measure should be filtering one status only. The total is correct but not per status.

As previously mentioned, this measure returns the correct result:

active =

CALCULATE(

COUNTA('table'[date_active])

,FILTER('table','table'[status] = "Active")

,USERELATIONSHIP(_calendar[reldate],'table'[date_active]))

Re: Suddenly Can't Filter in Measures

v-janeyg-msft — Wed, 06 Apr 2022 10:02:58 GMT

Hi, rusgesig

Can you provide a sample where the result is wrong? I can't see the problem without specific context.

Re: Suddenly Can't Filter in Measures

rusgesig — Wed, 06 Apr 2022 10:11:41 GMT

If you look at the measure, the count should be restricted to where 'status' is in 'Active', however it shows the 'Active' count for all values in 'status' even though it should be '0' for everything not in Active.

This is a simple count with a single filter - the filter doesn't apply correctly - don't know what else to provide in terms of context.

Re: Suddenly Can't Filter in Measures

tamerj1 — Wed, 06 Apr 2022 10:30:57 GMT

Hi rusgesig

you may try

Count of id for Active =
IF (
SELECTEDVALUE ( 'table'[status] ) = "Active",
DISTINCTCOUNT ( 'table'[id] )
)

Re: Suddenly Can't Filter in Measures

rusgesig — Wed, 06 Apr 2022 11:11:32 GMT

The problem is not finding a measure that works, it's why the syntax I have been using for years does not work anymore.

Re: Suddenly Can't Filter in Measures

tamerj1 — Wed, 06 Apr 2022 14:23:34 GMT

rusgesig

Well, it is doing what is supposed to do. It us replacing the filter on "Status" with the value "Active" as expected. Why do you think it should filter the visual?

Re: Suddenly Can't Filter in Measures

rusgesig — Wed, 06 Apr 2022 14:29:56 GMT

Because it used to.

Re: Suddenly Can't Filter in Measures

Whitewater100 — Wed, 06 Apr 2022 15:16:08 GMT

Hi:

I'm not sure if I understand. This is just an alternative way as DISTINCTCOUNT is not as effcient..I've read.

Table ID Active Count = CALCULATE(

COUNTROWS ( DISTINCT ( Table[ID] ),
FILTER(ALL(Table[Status]), Table[Status] = "Active")
)

Re: Suddenly Can't Filter in Measures

daXtreme — Wed, 06 Apr 2022 15:39:04 GMT

The golden rule of DAX programming: You should never filter a whole table if you can filter a column. If you do, you'll be in trouble sooner rather than later. In DAX, the fact that something works today does not guarantee it'll work tomorrow if you don't know what DAX really does under the hood.

[Your Measure] = calculate( distinctcount( 'table'[id] ), keepfilters( -- this is CRUCIAL for filter intersection! 'table'[status] = "active" ) )

Re: Suddenly Can't Filter in Measures

v-janeyg-msft — Thu, 07 Apr 2022 14:01:50 GMT

Hi, rusgesig

daXtreme 's right, this question is difficult to explain, in short, using filter(table,[column]="xxx") in the parameters of calculate() is much safer than using [column]="xxx" directly.

Best Regards,
Community Support Team _ Janey