Count number of times a value appears in a related table, with condition

[Rob_BTG]

I keep finding posts that don't quite answer this...

I have 2 tables that are conntected by the AppKey column, that is unique to Table 1. 

I would like to create a measure that tells me how many times the AppKey in Table 1, appears in Table 2, but only when the Status Code column in Table 2 is not blank.

Example:

Thanks in advance.

[Anonymous · Accepted solution]

This solution assumes that your blank columns are actually NULL and not ""  (empty string):

# Instances = 
CALCULATE(
	COUNTROWS(Table2)
	,Table2[Status Code] <> BLANK()
)

Table2[Status Code] is actually a blank string, convert it to NULL using Power Query.

If you can't do that, you can use this measure:

# Instances = 
CALCULATE(
	COUNTROWS(Table2)
	,Table2[Status Code] <> ""
)

I think you'll get better performance out of your model if it's NULL.

[Rob_BTG]

Thanks, that works.  I think you meant to say "Table 1" after COUNTROWS, I used Table 1 and it worked.

I may have had the columns backward in my previous attempts to use this.