DAX Formula Help - Should be easy!

[afaherty]

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!

[v-alq-msft]

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.

[mahoneypat · Accepted solution]

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]

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

HTH,

Smitty

[Anonymous]

Try the COUNTX in a new Measure:  

Measure = COUNTX(FILTER(Query1,[Campus]="MAIN"),[Term])

HTH,

Smitty

[afaherty]

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