COUNTIFS in Power BI with individual rows

[michaelmichael]

Hi there,

I'm currently trying to move my excel sheets over to Power BI, however I'm having trouble with some COUNTIFS.

My current excel formula is like so:

I'm currently using this formula in individual columns in Power BI:

NUMBER = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[YEAR] = "18" && 'Table'[CODE] = "CODE1"))

But I would like the Number to be calculated all in one column, with the "18" and "CODE1" to be chosen from the individual rows, as I have it in Excel.

Any help is much appreciated

[Mariusz · Accepted solution]

Hi @michaelmichael 

Try this.

NUMBER = 
    CALCULATE( 
        COUNTROWS( 'Table' ), 
        ALLEXCEPT( 
            'Table', 
            'Table'[CODE], 'Table'[YEAR]
        ) 
    )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[michaelmichael]

Thanks @Mariusz, that worked!

What if I wanted to use ALLEXCEPT and FILTER ?

i.e. I want to have the individual row filters, and also wanted to have a fixed filter such as "CODE1", etc

Something like so...?

NUMBER = CALCULATE(COUNTROWS( 'Table' ), ALLEXCEPT( 'Table', 'Table'[YEAR] ), FILTER('Table','Table'[CODE] = "CODE1"))

Regards,

Mike

[michaelmichael]

I think I figured it out by using the FILTER outside of the ALLEXCEPT

NUMBER=CALCULATE(COUNTROWS('Table'),FILTER(ALLEXCEPT('Table','Table'[YEAR]),'Table'[CODE]="CODE1"))

Thanks

[Mariusz]

Hi @michaelmichael 

Happy you worked it out.

Thanks

Mariusz