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!
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?:
FYI, both columns are measures so that may be why I'm having trouble. Nothing complicated, but in case you need them...
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"
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