cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jerime Frequent Visitor
Frequent Visitor

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

I have a table that is setup like below.

 

AreaAccountsNumber of Accounts with Disconnect Status
New York5885
Florida45530
Arkansas88821
California2348

 

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?

4 REPLIES 4
Super User
Super User

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

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
jerime Frequent Visitor
Frequent Visitor

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

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

AreaAccount
New York001
Florida002
Florida002
New York001

 

and

 

Data Table 2

AreaAccountStatusService
Florida002DisconnectedInternet
Florida002ConnectedInternet
Florida002DisconnectedPhone
New York001ConnectedInternet
New York001ConnectedPhone
New York001ConnectedTV

 

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)

AreaAccountsNumber of Accounts with Disconnected Status
New York12
Florida10

 

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.

jerime Frequent Visitor
Frequent Visitor

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

?

Community Support Team
Community Support Team

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

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
        )
    )

13.PNG

 

Regards,

Xiaoxin SHeng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,283)