Forum Discussion

damit23183's avatar
damit23183
Microsoft Employee
6 years ago

Count by Group not Working

Hi,

 

I have table below where i am trying to count CASE by Name column;

Table Name: Office_Table

 

 

 

 

 

 

 

 

 

I am trying to calculate COUNT OF CASES based on Certain Status and group by NAME.

Please see formula below that i did apply;

 

Total Case= CALCULATE (
DISTINCTCOUNT ( 'Office_Table'[Case #] ),
FILTER (
Office_Table',
NOT('Office_Table'[Status] In {"Appeal","Not Submitted","Pending","Active"}
)), ALLEXCEPT(Name_Table,Name_Table[Name]))

 

The answer i am getting is 31 which is incorrect because i should expect 10 only based on table above and condition applied in formula.

 

NOTE: In primary table there are total 31 cases which is correct but i am trying to count by group NAME. Also, Name is coming from different table and all other information are coming from primary table. The relationship between Primary and Secondary table is 1:Many because single case can have more than one name at a time.

 

Also there are possibility that Case can't have Name which you can see as NULL in NAME column. But, i would like to count Overall Case based on NAME. So if I select empty cell from Name column then i should see 5 in CARD.

 

Any help will be appreciated!

 

Thanks

Need Help

7 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    Could you please share the different tables, describe the business question and show the expected result?

    • damit23183's avatar
      damit23183
      Microsoft Employee
      6 years ago

      Hi Greg,

       

      Thanks for your response.

       

      I can't share my source data due to Privacy but the data i have created in EXCEL is same format that I have.

      I can never share my original data here so always try to create similar data in excel and paste it here.

       

      My apologies for that but can't do.

       

      Thanks

    • damit23183's avatar
      damit23183
      Microsoft Employee
      6 years ago

      Hi Amit, Thanks for your response. I am using ALLEXCEPT because i would like to COUNT of CASES group by NAME. In my scenario, there will total 3 COUNT Measure with 3 different GROUPS. It's like i am doing SQL Function:

       

       

      SELECT COUNT(CASES) as CNT

      FROM TABLE

      GROUP BY NAME

       

       

      Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    damit23183 

     

    The problem is allexcept, try remove it or replace with: 

     

    Total Case= CALCULATE (
DISTINCTCOUNT ( 'Office_Table'[Case #] ),
FILTER (
Office_Table',
NOT('Office_Table'[Status] In {"Appeal","Not Submitted","Pending","Active"}
)), FILTER(ALL(Name_Table'),SUMX(FILTER('Name_Table,EARLIER(Name_Table[Name])=Name_Table[Name]),1)

     

     

    Paul Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • damit23183's avatar
      damit23183
      Microsoft Employee
      6 years ago

      Hi,

       

      Well its not working as expected.

       

      However, thanks a lot for your inputs really appreciate it!

       

      Thanks