Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help with DAX Calculation

Hi all, if someone could help with the below DAX calc that would be awesome...   Ok... I have a list of study courses and via relationship I can determine the number of students in a course - easy...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Try

    formula 1
Count of student = SUM('Table'[student])

formula 2
count of courses = 
calculate(countx(
    VALUES(course[ID])  // Avg till this level
    , course[ID]
),[Count of student] >10)

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks.

    My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

amitchandak's avatar
amitchandak
Super User
6 years ago

Try

formula 1
Count of student = SUM('Table'[student])

formula 2
count of courses = 
calculate(countx(
    VALUES(course[ID])  // Avg till this level
    , course[ID]
),[Count of student] >10)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you for your help, unfortunately I get an error "CALCULATE has been used in a True/False expression that is used as a table filter expression".

     

    I think I need to make myself clearer as well...

    Ok I have 4 tables:

    Table 1 - a dimension that contains all courses (eg degree of computer science)

    Table 2 - a dimension that contains all topics that can be taken in that course (eg data modeling in power BI)

    Table 3 - a dimension of student information

    Table 4 - the fact table that joins it all together (eg, student 1 has enrolled in the power BI topic that is attached to the computer science course)

     

    Now what I am wanting to do is show a visual that tells the user the following:

    Axis = course name (eg degree of computer science)

    Value1 = total number of topics in a course (eg 10 topics in that course)

    value2 = total number of those topics that have less than 1 student enrolment. (eg 1 topic of the 10 has only 1 enrolment)

     

    I hope that helps - Thoughts?

     

    So I have already completed:

    - a distinct count of students - tick

    - a distinct count of topics - tick

    - And I can plot this in a visual against the course.

     

    I am missing, 

    - the number of topics where the student enrolment = 1

     

    Cheers

    • dax's avatar
      dax
      Community Support
      6 years ago

      Hi Patto,

      I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

      Please do mask sensitive data before uploading.

      Thanks for your understanding and support.
      Best Regards,
      Zoe Zhi