Count Occurrences based on conditions

[Elisa112]

Hello

I am trying to count the number meetings in a table based on conditions, here is my data

Meeting Table

Cust ID     Meeting ID  Meeting Date    Type           Status     

123           456               1 Jan                 Support      Attended

123           789               1 Feb                Support      Attended 

234           900               1 Jan                 Support     Attended 

788           677                2 Mar               Adhoc       Cancelled

Attended Meetings

Cust ID     No. Attended (Support)   No. Attended (Adhoc)

123                     2                                         0

234                     1                                         0

788                    0                                          0

The measure I created brings back the same number for all 

Count of Support Mtg =

CALCULATE(COUNT(Meetings2[Meeting ID]),FILTER(Meetings2, Meetings2[Type] = "Support Meeting"))

All help appreciated, thanks in advance

[Tahreem24 · Accepted solution]

@Elisa112 Thanks for the clarity. Create two measures as below:

Attended (Support) = CALCULATE(COUNT(Meeting[Status]),FILTER(Meeting,Meeting[Status]="Attended" && Meeting[Type]="Support"))+0

Attended (Adhoc) = CALCULATE(COUNT(Meeting[Status]),FILTER(Meeting,Meeting[Status]="Attended" && Meeting[Type]="Adhoc"))+0

[Anonymous · Accepted solution]

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

[Elisa112]

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

[Tahreem24]

@Elisa112 ,Please share the expected output  also.

[Elisa112]

Apologies, this the expected output

Attended Meetings

Cust ID     No. Attended (Support)   No. Attended (Adhoc)

123                     2                                         0

234                     1                                         0

788                     0                                          0

Thanks

[Tahreem24 · Accepted solution]

@Elisa112 Thanks for the clarity. Create two measures as below:

Attended (Support) = CALCULATE(COUNT(Meeting[Status]),FILTER(Meeting,Meeting[Status]="Attended" && Meeting[Type]="Support"))+0

Attended (Adhoc) = CALCULATE(COUNT(Meeting[Status]),FILTER(Meeting,Meeting[Status]="Attended" && Meeting[Type]="Adhoc"))+0

[Elisa112]

Thank you, I did use your solution in part but in the end I used a calculated column. but I appreciate your time