Forum Discussion

slhangen's avatar
slhangen
Icon for Helper II rankHelper II
5 years ago
Solved

Need count with multiple filters

I have created and Append table and need to get some calculations from it. Table is called Append1 and looks like following: TITLE STUDENT LAST NAME STUDENT FIRST NAME DESIGNATION COURSE TE...
  • edhans's avatar
    edhans
    5 years ago

    Take a look at the following slhangen - just one measure I think is all you need:

    Course Failures = 
COUNTX(
    FILTER(
        'Table',
        'Table'[SEMESTER GRADE] = "F"
    ),
    'Table'[SEMESTER GRADE]
)

    Simply add the fields I have added to a Table visual, then add the Course Failures measure I created. It will calculate the correct numbers per semester.

    If that is not what you need, could you be more specific in the output you require and the logic to get there?

edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
5 years ago

Take a look at the following slhangen - just one measure I think is all you need:

Course Failures = 
COUNTX(
    FILTER(
        'Table',
        'Table'[SEMESTER GRADE] = "F"
    ),
    'Table'[SEMESTER GRADE]
)

Simply add the fields I have added to a Table visual, then add the Course Failures measure I created. It will calculate the correct numbers per semester.

If that is not what you need, could you be more specific in the output you require and the logic to get there?

slhangen's avatar
slhangen
Icon for Helper II rankHelper II
5 years ago

This is amazing Ed, but I really need the following:

 

TITLE      STUDENT LAST NAME      STUDENT FIRST NAME     COURSE FAILURES- SEM 1     COURSE FAILURES- SEM 2

 

Can you help with this?  I would be forever on your debt, lol.

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Sure, that will require two measures:

     

    Course Failures Semester 1 = 
COUNTX(
    FILTER(
        'Table',
        'Table'[SEMESTER GRADE] = "F"
            && 'Table'[SEMESTER] = "SEM 1"
    ),
    'Table'[SEMESTER GRADE]
)

     

    and 

     

    Course Failures Semester 2 = 
COUNTX(
    FILTER(
        'Table',
        'Table'[SEMESTER GRADE] = "F"
            && 'Table'[SEMESTER] = "SEM 2"
    ),
    'Table'[SEMESTER GRADE]
)

     

    Then I got rid of the Semester field in the visual and added both of these measures, and I get this:

    Alternatively, you can keep my original measure and use a MATRIX visual instead:

    It has these fields:

    You need to do a few things to the matrix to make it work and look like I have it:

    • Turn off stepped layout
    • Turn off row subtotals
    • Drill all the way down on the matrix using the single-to-double down arrow when you hover over the matrix.

      •  

    • I like the matrix solution better because it is 1 measure and the semesters would dynamically expand (summer summester for example) without having to write a 3rd semester measure.

    But that is the cool thing about Power BI slhangen - different ways to do the same thing. Take your pick!

     

     

    • slhangen's avatar
      slhangen
      Icon for Helper II rankHelper II
      5 years ago

      I keep getting an error with this code. 
      Table is called Append1 (See screenshot).

       

       

      Course Failures Semester 1 =
      COUNTX(
      FILTER(
      'Append1',
      'Append1'[SEMESTER GRADE]="F"
      && Append1[SEMESTER]="SEM 1"
      ),
      'Append1'[SEMESTER GRADE]
      )
       

       


      Am I typing everything correct?

       

      THx so much for your time.

       

      • edhans's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        5 years ago

        There was no screenshot. What is the error? The only other suggestion I have with the info I have is to use this:

        Course Failures Semester 1 =
COUNTAX(
    FILTER(
        'Append1',
        'Append1'[SEMESTER GRADE] = "F"
            && Append1[SEMESTER] = "SEM 1"
    ),
    'Append1'[SEMESTER GRADE]
)

        COUNTAX can operate on a boolean data type, but COUNTX won't return an error, it just won't count the true/false values. But if you have text in the semester grade field, it shouldn't matter.

         

        Can you confirm that you have set the field data types correctly? [Semester Grade] and [Semester] should be text. In Power Query (The transform button in the home ribbon), ensure the icon in the upper left is ABC, not ABC/123.

        If it is ABC123, click it and select Text. Do that for every column. No columns brought into Power BI should he set to ABC123.


        Otherwise, you will need to share actual files via Dropbox or OneDrive that I can take a look at.