Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
Solved! Go to 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
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
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
@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])
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,
User | Count |
---|---|
117 | |
75 | |
62 | |
50 | |
44 |
User | Count |
---|---|
174 | |
125 | |
60 | |
60 | |
57 |