Forum Discussion
Count unique values with filter on another column
Hi,
I am having a table like
| Time | Country | Buiding ID | Status |
| 01_2021 | DE | A | open |
| 02_2021 | DE | A | open |
| 03_2021 | DE | A | open |
| 01_2021 | DE | B | closed |
| 02_2021 | DE | B | closed |
| 01_2021 | VN | C | open |
| 02_2021 | VN | C | open |
| 01 | VN | D | closed |
And I want to count the distinct building with status by country level
Country Open Closed
DE -------1------------1
VN-------1------------1
Is there any way to do it? please help, thank youuuu
Hi Anonymous ,
Actually, you can try use matrix table to do that without any measure.
first step, add country to Rows and status to Columns. And you will get this :
second, add the building ID to Values but set it is count(distinct) via the "v" icon. Refer the following.
Finally, the result:
Or measures:
open = calculate(DISTINCTCOUNT('Table'[Buiding ID]),'Table'[Status]="open")closed = CALCULATE(DISTINCTCOUNT('Table'[Buiding ID]),'Table'[Status]="closed")Pbix in the end you can refer.Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Anonymous Maybe:
Count Open Measure = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('Table',[Status] = "open"),"__Building",[Building])))Hi Anonymous ,
Actually, you can try use matrix table to do that without any measure.
first step, add country to Rows and status to Columns. And you will get this :
second, add the building ID to Values but set it is count(distinct) via the "v" icon. Refer the following.
Finally, the result:
Or measures:
open = calculate(DISTINCTCOUNT('Table'[Buiding ID]),'Table'[Status]="open")closed = CALCULATE(DISTINCTCOUNT('Table'[Buiding ID]),'Table'[Status]="closed")Pbix in the end you can refer.Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
