Forum Discussion

dte-chris's avatar
dte-chris
Icon for Helper I rankHelper I
6 years ago
Solved

COUNTROWS + FILTER and include zero values

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.
Need Help
  • dte-chris's avatar
    dte-chris
    6 years ago

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

10 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago
    Perhaps

    device_threats = VAR __Count = COUNTROWS(FILTER('ThreatOccurrencesInTimeframe', CONTAINS('DevicesOnline', [id], 'ThreatOccurrencesInTimeframe'[device_id])))
    RETURN
    IF(ISBLANK(__Count),0,__Count)
  • kentyler's avatar
    kentyler
    Icon for Solution Sage rankSolution Sage
    6 years ago

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

    • dte-chris's avatar
      dte-chris
      Icon for Helper I rankHelper I
      6 years ago

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

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      dte-chris
      Icon for Helper I rankHelper I
      6 years ago

      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.

  • lightsquared's avatar
    lightsquared
    New Member
    4 years ago

    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.