Forum Discussion
Count distinct values based on another column
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
Super UserTry
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 Linkedin
It's not working i am getting this result:
| Name | Open | Closed |
| Name1 | 3 | 5 |
| Name2 | 3 | 5 |
| Name3 | 3 | 5 |