Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Count Rows with conditions

Hello, I have the following data ID Opportunity Name Amount Stage Activity_ID Activity_Status 1 Opp1 450 Open 1 Completed 1 Opp1 450 Open 2 Open 2 Opp2 550 Open 3 Co...
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    According to my understanding, you want to count rows when

    1.the Stage is “Open” and

    2.the Acticity_ID is blank or the Activity_Status is “Open” , right?

     

    You could use the following formula:

     

    Column =
IF (
    'Opp'[Stage] = "Open"
        && (
            ISBLANK ( [Activity_ID] )
                || Opp[Activity_Status] = "Open"
        ),
    1,
    0
)
    count =
CALCULATE ( COUNTROWS ( 'Opp' ), FILTER ( ALL ( 'Opp' ), [Column] = 1 ) )

     

    My visualization looks like this:

    Did I answer your question ? Please mark my reply as solution. Thank you very much.

    If not, please upload some insensitive data samples and expected output.

     

    Best Regards,

    Eyelyn Qin

     

    Best Regards,

    Eyelyn Qin

amitchandak's avatar
amitchandak
Super User
6 years ago

Anonymous , try like


countx(filter(summarize(filter(Table, table[status] ="Open"), table[ID],"_1", calculate(count(table[ID]), isblank(table[Activity_Status]) || table[Activity_Status] ="completed")+0),[_1] =0),[ID])