Count distinct values based on another column

[Anonymous]

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

[v-kelly-msft · Accepted solution]

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

[Anonymous]

Hi v-kelly-msft,

It worked, thanks for help

Best Regards,
ksusser

[amitchandak]

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 Linkedin

[Anonymous]

It's not working i am getting this result:

Name	Open	Closed
Name1	3	5
Name2	3	5
Name3	3	5