How to get Distinct Count in one column based on another column with Distinct Count?

[jerime]

I have a table that is setup like below.

Area	Accounts	Number of Accounts with Disconnect Status
New York	588	5
Florida	455	30
Arkansas	888	21
California	234	8

In the "Accounts" column it is a distinct count of account numbers.  There are a lot of duplicates in this column because each row contains a record that could be any number of statuses.  The "Number of Accounts with Disconnected Status" is a count of a column that has a status which could be disconnected, connected, etc.  I am using the Drillthrough filters to pick the status of the account which is nice but the problem I have is that it gives me the count of all accounts in the "Accounts" column which has duplicates.  I need for it to only count the number of unique accounts with that status.  Any suggestions?

[Greg_Deckler]

I read through that, not getting it. 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

[jerime]

Sorry, I probably should have said that the example was my Power BI table.  The two related tables that I am using look like this.

 Data Table 1

Area	Account
New York	001
Florida	002
Florida	002
New York	001

and

Data Table 2

Area	Account	Status	Service
Florida	002	Disconnected	Internet
Florida	002	Connected	Internet
Florida	002	Disconnected	Phone
New York	001	Connected	Internet
New York	001	Connected	Phone
New York	001	Connected	TV

Now in my Power BI table I am counting the number of distinct accounts from column 2 in Data Table 1 for my Power BI table in the Accounts column.  In the last column, Number of Accounts with Disconnect Status, I am counting the

Power BI Table (pulling data from Data Table 1 & 2)

Area	Accounts	Number of Accounts with Disconnected Status
New York	1	2
Florida	1	0

The above is what I get in my Power BI table.  In the third column I want to get 1 instead of 2 because I'm looking for the number of distinct accounts that have the "Disconnected" status and not the number of times the "Disconnected" status appears in each "Area".  Of course filtering it further with the "Service" would be nice too, but baby steps.

[v-shex-msft]

HI @jerime,

You can try to use below measure to achieve your requirement.

Disconnected Status =
VAR currnet_area =
    SELECTEDVALUE ( 'Data Table 1'[Area] )
RETURN
    CALCULATE (
        COUNTROWS ( VALUES ( 'Data Table 2'[Account] ) ),
        FILTER (
            ALL ( 'Data Table 2' ),
            'Data Table 2'[Status] = "Disconnected"
                && [Area] = currnet_area
        )
    )

Regards,

Xiaoxin SHeng

[jerime]

?