Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 Te...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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

v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

Hi v-kelly-msft,

It worked, thanks for help

 

Best Regards,
ksusser