Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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")
)
Hi @rusgesig
you may try
Count of id for Active =
IF (
SELECTEDVALUE ( 'table'[status] ) = "Active",
DISTINCTCOUNT ( 'table'[id] )
)
The problem is not finding a measure that works, it's why the syntax I have been using for years does not work anymore.
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?
Because it used to.
This works, still really weird that the previous format stopped working - am I going to have go through old datasets to amend the measures.
Hi @rusgesig
please try
Count of id for Active =
CALCULATE ( DISTINCTCOUNT ( table[id] ), "Active" IN table[status] )
A function 'CONTAINSROW' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
Count of id for Active =
CALCULATE (
DISTINCTCOUNT ( table[id] ),
FILTER ( table, "Active" IN table[status] )
)
Yes, I said that works in my second post - my question is why the previous format stopped working all of a sudden?
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.
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:
Hi, @rusgesig
Can you provide a sample where the result is wrong? I can't see the problem without specific context.
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.
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"
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |