Forum Discussion

Elisa112's avatar
Elisa112
Icon for Helper V rankHelper V
1 year ago
Solved

Count Occurrences based on conditions

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           45...
  • Tahreem24's avatar
    Tahreem24
    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"))+0
     
    Attended (Adhoc) = CALCULATE(COUNT(Meeting[Status]),FILTER(Meeting,Meeting[Status]="Attended" && Meeting[Type]="Adhoc"))+0
     

     

     

     

  • Anonymous's avatar
    Anonymous
    1 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
        )
    ) + 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