cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bmurf Frequent Visitor
Frequent Visitor

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!

 

countrows.jpgcountrows2.jpg

3 REPLIES 3
vega Member
Member

Re: Counting rows that are NULL/BLANK

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?:

Capture.PNG

bmurf Frequent Visitor
Frequent Visitor

Re: Counting rows that are NULL/BLANK

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 Member
Member

Re: Counting rows that are NULL/BLANK

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