Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Counting values based on different conditions in multiple columns

Hello PowerBI Community!

 

I apologize if the title of this post is poorly worded, but I'm not exactly sure how to phrase my question without showing some sample data, so I've attached a screenshot below. What I would like to do is create a measure that counts the number of students who have attended a set of topics. For example, if my list of topics was Math and Science, I would need this measure to return 2 (John and Carlos have both attended Math and Science topics). I am fairly new to PowerBI, so any assistance would be greatly appreciated! Thanks in advance :smileyhappy:

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    You are right. By default, OR logic is applied on values selected in slicer. Based on your requirement, here is the updated measure. (Study is the Table Name)

     

    Measure=

    COUNTROWS(
    FILTER(
    ADDCOLUMNS(
    VALUES(Study[Student]),
    "Topics",CALCULATE(
    COUNTROWS(VALUES(Study[Topic])),
    Study[Attended]="Yes"
    )
    ),
    [Topics]=COUNTROWS(VALUES(Study[Topic]))
    )
    )
     
    Please give it a try and let's see if it serves the purpose.
     
    If this solves your problem, please mark it as a solution so that others can benefit. Have a great day.

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi,

    Just to give you an idea, try to create measure like this.

     

    Measure=Calculate
    Distinctcount('Table'[Student]),
    Filter(
    'Table',
    'Table'[Attended]="Yes"
    )
    )

     

    Use Topic in a slicer and measure will give you the count of students attending the selected subjects.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous , thank you for your solution. It seems to function correctly, but for one thing: I'm unable to select more than one topic in the slicer. Any suggestions on how to select more than one topic in the slicer? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago
        Hi,

        Please check the properties of slicer and look for Multi-select option. If it is set then you can press ctrl to select multiple values.
  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    Hi Anonymous ,

    You should be able to drop this on a Matrix, and then drop the Topic on a Filter.  However how did John attend and not attend Math?

     


    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Nathaniel_C 

       

      Hmm I'm not sure, maybe he was a liberal arts major :smileytongue:

       

      Anyways, I made a matrix with the distinct count of student as values, and put topic in the filters pane. As you can see below, when I select multiple topics, it applies the filter such that it counts the number of students who have attended EITHER of the topics. However, my goal is to count the number of students who have attended BOTH of the topics. I have a feeling there's something simple I'm missing - any help figuring it out would be appreciated!