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 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