IF with multplie accounts

[KW123]

Hi, 

I am having a hard time writing out my IF statement. 

We have unique Customer ID's [CustID] and within those ID's, customers can have different types of accounts [AccountNumber].  We alsohave a column which shows if the accounts are open or closed [Account status]

I want to have a visualization that shows only Customer ID's with all accounts closed.  If I filter out Account Status to "Closed" it will still show Customer ID's with open accounts, but because they have at least one closed account, it is counting it in the filter.  

So the IF statement would be something like
IF customer ID has closed and open accounts, make it "active" 
IF customer ID only has closed accounts, make it "inactive" 

So in the example below, only CustomerID 456 would show on the visualization because Customer 123 still has an open account. 

Cust ID	Account Number	Account Status
123	111	Open
123	112	Closed
456	113	Closed
789	114	Open

[KW123]

@Anonymous 

Thank you so much!!! This worked 

[Anonymous · Accepted solution]

hello @KW123 ,
please try:

Measure = IF(CALCULATE(SELECTEDVALUE(Table1[Account Status]),ALLEXCEPT(Table1,Table1[Cust ID])) = "Closed","Inactive","Active")

 

if you found this helpful, please consider accepting it as a solution and a kudos is appreciated

[KW123]

@Anonymous 
Do you have a suggestion for what to do in this case? 

I'd like an IF statement which will show Customer ID's with a total current account balance of $0.00. Similar to above, each Customer ID can have multiple account types with different balances.  I need to pull only the Customer ID with a current total balance of $0.00. 

Customer ID	Account Number	Current Balance
1	111	$0.00
1	112	$0.00
2	113	$100
2	114	$0.00
2	115	$200
3	116	$0.00
4	117	$500
5	118	$0.00
5	119	$0.00

So only customer ID 1, 3, 5 should show up in our filter as their total account balance is $0.00 for this filtered month. 

Thank you!