Forum Discussion
Count Occurrences based on conditions
- 1 year ago
Elisa112 Thanks for the clarity. Create two measures as below:
Attended (Support) = CALCULATE(COUNT(Meeting[Status]),FILTER(Meeting,Meeting[Status]="Attended" && Meeting[Type]="Support"))+0Attended (Adhoc) = CALCULATE(COUNT(Meeting[Status]),FILTER(Meeting,Meeting[Status]="Attended" && Meeting[Type]="Adhoc"))+0 - Anonymous1 year ago
Hi, Tahreem24 ,thanks for your concern about this issue.
Your answer is excellent!
And I would like to share some additional solutions below.Hello,Elisa112 .I am glad to help you.
I tried the measure written by Tahreem24 and the logic of the measure calculation is correct, however there are some small limitations: the external filter conditions are not removed in the filter function, so the data in each row of the table visual also affects the calculation environment of the measure (affected by the current row)
You can see that when only one Cust ID field is placed in the table visual, the measure is calculated correctly, but when more than one field is placed in the table, the calculation environment is affected and the measure is displayed as four rows (instead of the desired aggregation effect below)My suggestions.
So I've made some small improvements to this, I've added the ALL function to remove all external filters, but of course if you have other slicer filtered fields you can try to use ALLSELECTED() (which ensures that the filtering of the fields in the slicer is passed to the measure)
And added grouping logic to the measure (grouping based on [Cust ID])If you want to use the slicer to control the result of the calculation of the measure, you can use ALLSELECTED() instead of the ALL() function
like this:This is my measure:
M_Attend_Support = VAR _custID =MAX('Meetings2'[Cust ID]) // _custID:Get the Cust ID value for each row RETURN CALCULATE ( COUNT ( 'Meetings2'[Status] ), FILTER ( ALL(Meetings2), 'Meetings2'[Status] = "Attended" && 'Meetings2'[Type] = "Support" && 'Meetings2'[Cust ID] = _custID ) ) + 0 M_Attend_Adhoc = VAR _custID =MAX('Meetings2'[Cust ID]) // _custID:Get the Cust ID value for each row RETURN CALCULATE ( COUNT ( 'Meetings2'[Status] ), FILTER ( ALL(Meetings2), 'Meetings2'[Status] = "Attended" && 'Meetings2'[Type] = "Adhoc" && 'Meetings2'[Cust ID] = _custID ) ) + 0ALLSELECTED function:
M_ALLSELECTED_Attend_Support = VAR _custID =MAX('Meetings2'[Cust ID]) // _custID:Get the Cust ID value for each row RETURN CALCULATE ( COUNT ( 'Meetings2'[Status] ), FILTER ( ALLSELECTED(Meetings2), 'Meetings2'[Status] = "Attended" && 'Meetings2'[Type] = "Support" && 'Meetings2'[Cust ID] = _custID ) ) + 0
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian
Hi, Tahreem24 ,thanks for your concern about this issue.
Your answer is excellent!
And I would like to share some additional solutions below.
Hello,Elisa112 .I am glad to help you.
I tried the measure written by Tahreem24 and the logic of the measure calculation is correct, however there are some small limitations: the external filter conditions are not removed in the filter function, so the data in each row of the table visual also affects the calculation environment of the measure (affected by the current row)
You can see that when only one Cust ID field is placed in the table visual, the measure is calculated correctly, but when more than one field is placed in the table, the calculation environment is affected and the measure is displayed as four rows (instead of the desired aggregation effect below)
My suggestions.
So I've made some small improvements to this, I've added the ALL function to remove all external filters, but of course if you have other slicer filtered fields you can try to use ALLSELECTED() (which ensures that the filtering of the fields in the slicer is passed to the measure)
And added grouping logic to the measure (grouping based on [Cust ID])
If you want to use the slicer to control the result of the calculation of the measure, you can use ALLSELECTED() instead of the ALL() function
like this:
This is my measure:
M_Attend_Support =
VAR _custID =MAX('Meetings2'[Cust ID])
// _custID:Get the Cust ID value for each row
RETURN
CALCULATE (
COUNT ( 'Meetings2'[Status] ),
FILTER (
ALL(Meetings2),
'Meetings2'[Status] = "Attended"
&& 'Meetings2'[Type] = "Support"
&& 'Meetings2'[Cust ID] = _custID
)
) + 0
M_Attend_Adhoc =
VAR _custID =MAX('Meetings2'[Cust ID])
// _custID:Get the Cust ID value for each row
RETURN
CALCULATE (
COUNT ( 'Meetings2'[Status] ),
FILTER (
ALL(Meetings2),
'Meetings2'[Status] = "Attended"
&& 'Meetings2'[Type] = "Adhoc"
&& 'Meetings2'[Cust ID] = _custID
)
) + 0
ALLSELECTED function:
M_ALLSELECTED_Attend_Support =
VAR _custID =MAX('Meetings2'[Cust ID])
// _custID:Get the Cust ID value for each row
RETURN
CALCULATE (
COUNT ( 'Meetings2'[Status] ),
FILTER (
ALLSELECTED(Meetings2),
'Meetings2'[Status] = "Attended"
&& 'Meetings2'[Type] = "Support"
&& 'Meetings2'[Cust ID] = _custID
)
) + 0
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian
- Elisa1121 year ago
Helper V
Thanks for your assistance on this, I actually used the following which I found from a previous thread, the main difference is I created a calcuated column, although I believe a measure is more dynamic:
Count Support Mtg Attended = COUNTROWS(FILTER(Meetings, Meetings[Cust ID] = EARLIER('Meetings'[Cust ID]) && 'Meetings'[Type] = "Support" && 'Meetings'[Status] = "Attended"))Thank you