Forum Discussion
Count distinct values based on another column
Hello experts,
I'm just starting with Power BI and i cant get over one thing. I have a table with columns "Name" "Issue" "Status"
It looks like this:
| Name | Issue | Status |
Name1 | Test1 | Open |
| Name1 | Test2 | Open |
| Name1 | Test3 | Closed |
| Name2 | Test4 | Closed |
| Name2 | Test5 | Closed |
| Name2 | Test6 | Closed |
| Name2 | Test7 | Open |
| Name3 | Test8 | Closed |
And i want a summary for each value in the Name column with count of the values in Status Column
Result should look like this:
| Name | Open | Closed |
| Name1 | 2 | 1 |
| Name2 | 1 | 3 |
| Name3 | 0 | 1 |
Any help there? I'm was searching through community posts but nothing worked for me
Hi Anonymous ,
You need 2 measures as below:
Open = CALCULATE(COUNT('Table'[Status])+0,ALLEXCEPT('Table','Table'[Name]),'Table'[Status]="Open")close = CALCULATE(COUNT('Table'[Status])+0,ALLEXCEPT('Table','Table'[Name]),'Table'[Status]="Closed")Finally you will see:
Or you can create a new table,using a dax expression as below:
Table 2 = SUMMARIZE ( 'Table', 'Table'[Name], "Open", CALCULATE ( COUNT ( 'Table'[Status] ) + 0, ALLEXCEPT ( 'Table', 'Table'[Name] ), 'Table'[Status] = "Open" ), "Close", CALCULATE ( COUNT ( 'Table'[Status] ) + 0, ALLEXCEPT ( 'Table', 'Table'[Name] ), 'Table'[Status] = "Closed" ) )Finally,you will see:
For the related .pbix file,pls click here.
Best Regards,
Kelly
4 Replies
Try
Open = calculate(count(table[issue]), table[Status]="Open") Closed calculate(count(table[issue]), table[Status]="Closed") issue =count(table[issue])The first two measures you can use in table or matrix with name in row.
The third one you can use in matrix name in row and issue in a col
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on LinkedinHi Anonymous ,
You need 2 measures as below:
Open = CALCULATE(COUNT('Table'[Status])+0,ALLEXCEPT('Table','Table'[Name]),'Table'[Status]="Open")close = CALCULATE(COUNT('Table'[Status])+0,ALLEXCEPT('Table','Table'[Name]),'Table'[Status]="Closed")Finally you will see:
Or you can create a new table,using a dax expression as below:
Table 2 = SUMMARIZE ( 'Table', 'Table'[Name], "Open", CALCULATE ( COUNT ( 'Table'[Status] ) + 0, ALLEXCEPT ( 'Table', 'Table'[Name] ), 'Table'[Status] = "Open" ), "Close", CALCULATE ( COUNT ( 'Table'[Status] ) + 0, ALLEXCEPT ( 'Table', 'Table'[Name] ), 'Table'[Status] = "Closed" ) )Finally,you will see:
For the related .pbix file,pls click here.
Best Regards,
Kelly
