Forum Discussion

afaherty's avatar
afaherty
Helper V
6 years ago
Solved

DAX Formula Help - Should be easy!

Hello!

I am fairly new to PowerBI and DAX as well.  What I am trying to accomplish is a column to calculate the number of times the "MAIN" campus appears for Fall 2019 and Spring 2020 (from the "Term" Column).  Same for the "OTHTU" and "OTHEX" campuses.  There is more to the dataset, but this is the only part I'm having troubles with.  Can anyone help?  Screenshot below - I figured the PBIX file wouldn't be needed.  Thanks SO much!

 

Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Have you tried making a matrix visual with Term on the rows, Campus on the columns, and making a simple measure like Campus Count = COUNTROWS(Table)?  Your post requested a column, but is that needed?  Many people do more columns than necessary when they first start out, which is why I'm asking.

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    Have you tried making a matrix visual with Term on the rows, Campus on the columns, and making a simple measure like Campus Count = COUNTROWS(Table)?  Your post requested a column, but is that needed?  Many people do more columns than necessary when they first start out, which is why I'm asking.

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Try the COUNTX in a new Measure:  

    Measure = COUNTX(FILTER(Query1,[Campus]="MAIN"),[Term])
    HTH,
    Smitty
    • afaherty's avatar
      afaherty
      Helper V
      6 years ago

      Anonymous Thanks! How would I nest the other 2 Campuses (OTHTU and OTHEX) within that?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Use the logical operator || (OR) to combine additional values in your FILTER
    Measure = COUNTX(FILTER(Query1,[Campus]="MAIN" || [Campus]="OTHEX"),[Term])

    HTH,

    Smitty

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, afaherty 


    Based on your description, I created data to reproduce your scenario.

    Table:

     

    You may try measures as below.

    NumMain = 
COUNTROWS(
    FILTER(
        ALL('Table'),
        OR(
            'Table'[Term]="Spring 2020",
            'Table'[Term]="Fall 2019"
        )&&
        'Table'[Campus]="MAIN"
    )
)

NumOTHEX = 
COUNTROWS(
    FILTER(
        ALL('Table'),
        OR(
            'Table'[Term]="Spring 2020",
            'Table'[Term]="Fall 2019"
        )&&
        'Table'[Campus]="OTHEX"
    )
)

NumOTHTU = 
COUNTROWS(
    FILTER(
        ALL('Table'),
        OR(
            'Table'[Term]="Spring 2020",
            'Table'[Term]="Fall 2019"
        )&&
        'Table'[Campus]="OTHTU"
    )
)

     

    Result:

     

    Best Regards

    Allan

     

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