Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count DAX

Hi Community, I have the following table: Project ID Project Sub ID Value 100 100A 10 100 100B 20 100 100B 30 101 101A 40 102 102A 60 102 102B 80 102 102C 90...
Need Help
Show and Tell
Tutorial Requests
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    Anonymous 

    please try to create a measure

    Measure = CALCULATE(DISTINCTCOUNT('Table'[Project Sub ID]),FILTER(all('Table'),'Table'[Project ID]=max('Table'[Project ID])))

    pls see the attachment below

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thank you for your response, sorry I should have clarified more. What I am trying to do is to count the number of subcategories in each project ID and assign that value to each Sub ID. 100 has 2 distinct categories (102A and 102B), so both 102A and 102B will have 2 as value. 103 has 3 distinct categories (103A, 103B, 103C) so 103A, 103B, 103C will have the 3 as value. 

ryan_mayu's avatar
ryan_mayu
Super User
5 years ago

Anonymous 

please try to create a measure

Measure = CALCULATE(DISTINCTCOUNT('Table'[Project Sub ID]),FILTER(all('Table'),'Table'[Project ID]=max('Table'[Project ID])))

pls see the attachment below

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thank you so much for help! Could you also provide me the explaination of how the measure above works so I could use it as a future reference?

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      5 years ago

      Anonymous 

      Measure = CALCULATE(DISTINCTCOUNT('Table'[Project Sub ID]),FILTER(all('Table'),'Table'[Project ID]=max('Table'[Project ID])))

      if we have subject ID, then we can get the corresponding project ID.

      FILTER(all table)- remove filters to search the whole table.

      then search the project IDs which are the same as the current one.

      max or min will return the current project ID