Forum Discussion

bmurf's avatar
bmurf
Frequent Visitor
9 years ago

Counting rows that are NULL/BLANK

Hello!

I'm trying to create a measure that will give me a count of the # of people who donated last year "T&O-ALL(LY)" but did not donate this year "T&O-ALL." My results kept coming back BLANK, so I created a table with filters to compare. 

I've included a screenshot below which shows the problem.

 

I can't figure out why the "Count of User ID" is BLANK for some rows and "1" for others given the same filter criteria!

 

The 2nd screenshot shows that if I increase the "T&O-All" filter amount so that there is an actual match, the Count shows up - the I the count when "T&O-All" = 0!

 

I think if I solve this problem I can create the measure to match it...any insights? Thanks for the help!

 

Need Help

3 Replies

  • vega's avatar
    vega
    Icon for Resolver III rankResolver III
    9 years ago

    So you want "Count User Id" to count the instances when there is a value in "T&O-ALL(LY)" and not a value in "T&O-ALL", based on "User Id", and blank (or zero) otherwise? 

    Something like this?:


    • bmurf's avatar
      bmurf
      Frequent Visitor
      9 years ago

      Yes, exactly!
      FYI, both columns are measures so that may be why I'm having trouble.  Nothing complicated, but in case you need them...

       

      Thanks!


      T&O-All = [Online T&O]+[Reg T&O]

      T&O-All (LY) = CALCULATE([T&O-All], SAMEPERIODLASTYEAR(dCalendar[Date]))

      Reg T&O = CALCULATE(SUM(Import_Financial_Hist[Amount]),

      'Import_Financial_Hist'[Purpose Code]="Tithes & Offerings"
      )  

      Online T&O = CALCULATE(SUM('Import_Financial_Hist'[Amount]),
      'Import_Financial_Hist'[Purpose Code]="Tithes & Offerings Online" ||
      'Import_Financial_Hist'[Purpose Code]="Online Tithes & Offerings"
      )

      • vega's avatar
        vega
        Icon for Resolver III rankResolver III
        9 years ago

        It's hard without the data in front of me, but would something like the following not work?:

        Count of User Id = 
COUNTROWS(
    FILTER(
        Table1,
        AND(
            ISBLANK([T&O-All]),
            NOT(ISBLANK([T&O-All (LY)]))
        )
    )
)

        Where Table 1 is the table that contains the User Id