Count Rows with conditions

[Anonymous]

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	Completed
2	Opp2	550	Open	4	Completed
3	Opp3	320	Closed Won	5	Completed
4	Opp4	230	Closed Won	6	Completed
5	Opp5	780	Closed Won	7	Completed
6	OPP6	980	Open	8	Completed
6	OPP6	980	Open	9	Completed
7	OPP7	760	Open
8	OPP8	345	Open
9	Opp9	657	Closed Won	10	Completed
10	OPP10	879	Open	11	Open
11	OPP11	687	Open	12	Completed
12	OPP12	4678	Open	13	Completed
13	OPP13	1500	Open	14	Open
14	Opp14	3400	Closed Won	15	Completed
15	Opp15	4388	Closed Lost	16	Completed
16	Opp16	3400	Closed Lost	17	Completed
17	OPP17	9000	Open	18	Open
18	OPP18	4000	Open	19	Open
19	OPP19	3000	Open	20	Open
20	Opp20	1000	Closed Won	21	Completed

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 · Accepted solution]

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]

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

[amitchandak]

@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]

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,