Count of Occurences and Filters not working as expected

[sophielred]

Hi! I have a table of data which stores customer relationship data. Important columns are as below:

• Account Number
• Expiration Date
• Expiration Option
• Site Name
  
  

Customers can select more than one Expiration Option for a single account number: withdraw partial tokens and transfer partial tokens to existing account. This results in duplicate account numbers in the table if the customer has selected more than one expiration option.

I am trying to calculate the below:

If Account Number count = 1 and Expiration Option is Withdraw All then "Withdrawal - Relationship Ending"

Or Account Number count = 1 and Expiration Option is Close Account then "Closure - Relationship Ending"

Else "Continuing Relationship"

I have done this in the following way:

Step 1
Unique Accounts Count = distinctcount(CUST_QUERY[ACCOUNT_NUMBER])

Step 2

Account Number Count = COUNTROWS(FILTER(CUST_QUERY,EARLIER(CUST_QUERY[ACCOUNT_NUMBER])=CUST_QUERY[ACCOUNT_NUMBER]))

Step 3
Account Status = SWITCH(

TRUE(),

CUST_QUERY[Account Number Count] = 1 && CUST_QUERY[EXPIRATION_OPTION] = "Withdraw All", "Withdrawal - Relationship Ending",

CUST_QUERY[Account Number Count] = 1 && CUST_QUERY[EXPIRATION_OPTION] = "Close Account", "Closure - Relationship Ending",

"Retained Relationship")

I've then created a table like below:

Status	Unique Accounts Count
Withdrawal - Relationship Ending	10
Closure - Relationship Ending	5
Continuing Relationship	50

The results are correct if I do not apply any filters in the Report View, however I need to be able to filter on Expiration Date and Site Name. As soon as I apply a filter in the filter pane, the results are incorrect.

I'd be very appreciative of any advice.

[Greg_Deckler]

@sophielred Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.