Forum Discussion

sardo's avatar
sardo
Regular Visitor
3 years ago
Solved

DAX Formula: Count

Hi there,

I'm new to PowerBI and I have a DAX formula that does not give the correct results. In my ProjectsTable, I have a list of all Projects with or without Milestones and each Milestone have an ID and Name, which means I have one line for each Milestone.

 

Here is the Incorrect DAX:

CALCULATE (ProjectsTable),

ALLSELECTED(ProjectsTable[Milestones]),

VALUES (ProjectsTable[Milestones]

)

 

It gives the incorrect count and shows 1 Milestone when it should be zero.

What should be the correct DAX Formula?

 

Thank you in advance for your help.

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi sardo ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) This is my test data. 

    (2) We can create a measure. 

    ProjectsWithMilestones = 
CALCULATE (
    DISTINCTCOUNT ( ProjectsTable[ProjectID] ),
FILTER(ALLSELECTED('ProjectsTable'),'ProjectsTable'[Milestones]<>BLANK()))

    (3) Then the result is as follows.

     

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

     

    Best Regards,

    Neeko Tang

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi sardo ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) This is my test data. 

    (2) We can create a measure. 

    ProjectsWithMilestones = 
CALCULATE (
    DISTINCTCOUNT ( ProjectsTable[ProjectID] ),
FILTER(ALLSELECTED('ProjectsTable'),'ProjectsTable'[Milestones]<>BLANK()))

    (3) Then the result is as follows.

     

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

     

    Best Regards,

    Neeko Tang

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

    • sardo's avatar
      sardo
      Regular Visitor
      3 years ago

      Thank you so much for your quick response.

      I've tried this DAX measure and it worked!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    What exactly are you trying to do?  Share some data, explain the question and show the expected result.