Countifs formula in Power BI

[Edmundas]

Please help me to write an excel COUNTIFS formula in Power BI table column. I've tried various ways to make it work with FILTER, COUNTROWS and etc. but didn't get desired result.

I have a table (below) and need to find patients who checked out or checked in at the clinic during January 2022 - March 2022. 

Thanks in advance!

 

[Edmundas]

It worked!!! Thank you so much

[Anonymous · Accepted solution]

Hi  @Edmundas ,

Here are the steps you can follow：

1. In Power query. Add Column – Index Column – From 1.

2. Create calculated column

Countifs =
VAR _count =
    COUNTX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Doctor] = EARLIER ( 'Table'[Doctor] )
                && YEAR ( 'Table'[Period] ) = YEAR ( EARLIER ( 'Table'[Period] ) )
                && MONTH ( 'Table'[Period] ) = MONTH ( EARLIER ( 'Table'[Period] ) )
        ),
        [Doctor]
    )
VAR _if =
    IF (
        MONTH ( 'Table'[Period] ) >= 1
            && MONTH ( 'Table'[Period] ) <= 3
            && YEAR ( 'Table'[Period] ) = 2022,
        _count,
        0
    )
RETURN
    IF (
        _if < 2,
        _if,
        IF (
            _if >= 2
                && 'Table'[Index]
                    = MINX (
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[Doctor] = EARLIER ( 'Table'[Doctor] )
                                && YEAR ( 'Table'[Period] ) = YEAR ( EARLIER ( 'Table'[Period] ) )
                                && MONTH ( 'Table'[Period] ) = MONTH ( EARLIER ( 'Table'[Period] ) )
                        ),
                        [Index]
                    ),
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Doctor] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Doctor] = EARLIER ( 'Table'[Doctor] )
                        && YEAR ( 'Table'[Period] ) = YEAR ( EARLIER ( 'Table'[Period] ) )
                        && MONTH ( 'Table'[Period] ) = MONTH ( EARLIER ( 'Table'[Period] ) )
                )
            ),
            0
        )
    )

3. Result:

Best Regards,

Liu Yang

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