Row count of a measure result that meets a criteria

[vpatel55]

Hello,

I have created a measure called "Proposed total cost change". It works over several tables, and works well. It outputs both positive and negative values. 

I need a second measure that counts only the positive values in the matrix. Here is the code:

CountPositiveValues = 
COUNTROWS(
    FILTER (
        SUMMARIZE (
            AgentsImport,
            AgentsImport[Agent Name],
            "COUNT", [Proposed total cost change]
        ),
        [COUNT]>0
    )
)

However, it does not work. It seems to be counting every value, not just those above zero. I created a diagnostic table to see what is happening. It seems that rather than filtering by Agent Name, the measure is outputing the grand total for every Agent. That is why it seems to be counting every value, as they are all above zero.

This repeating pattern usually occurs when a table has not been linked correctly, however "Proposed total cost change" is a measure, and its relationships are already implicit, as is clear in the first matrix? So any guidance here would be great.

I can't easily send the source file because of confidential data. But any help on counting just the positive values would be appreciated.

Thanks.

[parry2k · Accepted solution]

@vpatel55 try this

Positive Count = 
SUMX ( VALUES ( AgentTable[AgentName] ), IF ( [Your Measure] > 0, 1, 0 ) )

Would appreciate Kudos 🙂 if my solution helped.

[vpatel55]

Thanks @parry2k for the quick response. I'm afraid it seems to give exactly the same output as my measure. Here is a screenshot, where your measure is "CountPositivesValues2", and mine is "CountPositivesValues1". It still seems to be counting all 377 agents.

However maybe we can work with this, because your measure (CountPositiveValues2) does seem to classify only those that are over zero? It's just that those that are classified, as classed as "377". So perhaps a count of nonzeros of your measure?

Although there may be a cleaner way of doing it? Thanks 🙂

[parry2k]

@vpatel55 can you share pbix file, remove sensitive information before sharing. seems like i'm missing something, it should work based on info you provided.

[vpatel55]

I've had a faceplam moment! While I was removing the sensitive information, I noticed that I have two agent tables, and I had used one agent table in the the primary measure, and the wrong agent table in the count measure! When I used the correct one, both of our solutions worked perfectly, but yours is much more elegant! Kudos to you sir 🙂