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.