COUNTROWS + FILTER and include zero values

[dte-chris]

I am trying to use a filtered row count, but it's not displaying rows resulting in zero rows counted.

Here is my measure formula:

device_threats = COUNTROWS(FILTER('ThreatOccurrencesInTimeframe', CONTAINS('DevicesOnline', [id], 'ThreatOccurrencesInTimeframe'[device_id])))

I tried adding a "+ 0" to the end like this:

device_threats = COUNTROWS(FILTER('ThreatOccurrencesInTimeframe', CONTAINS('DevicesOnline', [id], 'ThreatOccurrencesInTimeframe'[device_id]))) + 0

But that now causes it to ignore the FILTER statement entirely, and now it's including the count of ALL rows completely unfiltered.

Edit: I'll clarify a little better. With my first formula, the DevicesOnline table in the report gets filtered down just fine when I select one of its parent records, but it does not display devices with threat count zero. With my second formula, the DevicesOnline table in the report is always displaying all records at all times, even if I select a parent record it keeps showing everything, like it's now completely ignoring the relationship.

[lightsquared]

I found this to work:

CCV Count =

IF (

CALCULATE( COUNT ( 'ALT Monthly Critical Control Data'[Overdue CCV] ), 'ALT Monthly Critical Control Data'[Asset Status] = "ACTIVE",'ALT Monthly Critical Control Data'[Overdue CCV] < 0,'ALT Monthly Critical Control Data'[RMM Status] = BLANK()) = BLANK(),

0,

CALCULATE( COUNT ( 'ALT Monthly Critical Control Data'[Overdue CCV] ), 'ALT Monthly Critical Control Data'[Asset Status] = "ACTIVE",'ALT Monthly Critical Control Data'[Overdue CCV] < 0,'ALT Monthly Critical Control Data'[RMM Status] = BLANK()))

It essentailly performs a count on filtered data and if the value is BLANK() then replace with a 0 else peform the count on filtered data normally.

[az38]

Hi @dte-chris 

are you sure you want to calculate countrows in 'ThreatOccurrencesInTimeframe' table based on 'DevicesOnline' table columns values? do you have appropriate relationships?

[dte-chris]

@az38 Sorry, maybe I should have written that better. I do have a relationship. What's happening with the +0 is when I do that, the DevicesOnline starts ignoring filtering when I click on a related parent record. With my first query, everything works except rows with zeros are not displayed, and they get filtered when I click on a parent record in the report. With my second query, my DevicesOnline table in the report displays the entire table at all times, not just the ones related to the selected parent.

[kentyler]

Do you have a relationship in your data model between ThreatOccurrencesInTimeframe and DevicesOnline ?

[dte-chris]

@kentyler Sorry, yes there is a one-to-many relationship from DevicesOnline to ThreatOccurrencesInTimeframe.

[Greg_Deckler]

Perhaps

device_threats = VAR __Count = COUNTROWS(FILTER('ThreatOccurrencesInTimeframe', CONTAINS('DevicesOnline', [id], 'ThreatOccurrencesInTimeframe'[device_id])))
RETURN
IF(ISBLANK(__Count),0,__Count)

[dte-chris]

@Greg_Deckler That didn't seem to change anything.

[Greg_Deckler]

Try clicking on your column in Values area and choose "Show items with no data"?

[dte-chris]

@Greg_Deckler That does not appear as an option. Also, read the edit to my OP.

[dte-chris · Accepted solution]

I've given up on trying to get a measure to work and decided to just count it with an M query.