Forum Discussion
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
| 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.