Suddenly Can't Filter in Measures

[rusgesig]

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.

[Whitewater100]

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")
)

[tamerj1]

Hi @rusgesig 

you may try

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

[rusgesig]

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

[tamerj1]

@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?

[rusgesig]

Because it used to.

[v-janeyg-msft]

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

[rusgesig]

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]))

[tamerj1]

Hi @rusgesig 

please try

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

[rusgesig]

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

[tamerj1]

@rusgesig 

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

[rusgesig]

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

[v-janeyg-msft]

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.

[rusgesig]

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]))

[v-janeyg-msft]

Hi, @rusgesig 

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

[rusgesig]

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.

[daXtreme]

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"
    )
)