Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count Rows with conditions

Hello,

I have the following data

IDOpportunity NameAmountStageActivity_IDActivity_Status
1Opp1450Open1Completed
1Opp1450Open2Open
2Opp2550Open3Completed
2Opp2550Open4Completed
3Opp3320Closed Won5Completed
4Opp4230Closed Won6Completed
5Opp5780Closed Won7Completed
6OPP6980Open8Completed
6OPP6980Open9Completed
7OPP7760Open  
8OPP8345Open  
9Opp9657Closed Won10Completed
10OPP10879Open11Open
11OPP11687Open12Completed
12OPP124678Open13Completed
13OPP131500Open14Open
14Opp143400Closed Won15Completed
15Opp154388Closed Lost16Completed
16Opp163400Closed Lost17Completed
17OPP179000Open18Open
18OPP184000Open19Open
19OPP193000Open20Open
20Opp201000Closed Won21Completed

 

I need to calculate the number of Opportunitites that are still in stage "open" but either do not have any related activity or their related activities are "completed" and they do not have any further open activities

 

please support

  • 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

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 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])

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello

      I tried the measure but it's giving me the following error ; too few arguments were passed to filter function. the minimum arguments count for the fucntion is 2

       

      please support

       

      regards,

  • 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