Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello,

    the resuklt is not correct as it should read 6 and not 8.

    Opportunity 1 is included while it should not as it has already an open activity.

    and we should not count more than one the same opportunity.

     

    thank you

     

    regards,

    Imbrg