Countifs in DAX

[Spartanos]

Hi,

I want to calculate the countifs on time and to late per country, per tracking nr and per week. Next, I would like to calculate a % of being on time per week and per country.

Current data                                                                                                 

Tracking            On-time-check          week        country                             

11222                 yes                           14            DE                                     

11222                 no                            14            DE                                      

11232                 yes                           14            SE                                       

11232                 no                            14            SE

Desired outcome
Tracking week country performace

11222    14      DE         50%

11232    14      SE         50%

[v-yanjiang-msft · Accepted solution]

Hi @Spartanos ,

According to your description, here's my solution.

Create a measure.

Performance =
VAR _CY =
    COUNTROWS (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Tracking] = MAX ( 'Table'[Tracking] )
                && 'Table'[Week] = MAX ( 'Table'[Week] )
                && 'Table'[Country] = MAX ( 'Table'[Country] )
                && 'Table'[On-time-check] = "yes"
        )
    )
VAR _C =
    COUNTROWS (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Tracking] = MAX ( 'Table'[Tracking] )
                && 'Table'[Week] = MAX ( 'Table'[Week] )
                && 'Table'[Country] = MAX ( 'Table'[Country] )
        )
    )
RETURN
    DIVIDE ( _CY, _C )

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[Greg_Deckler]

@Spartanos 

COUNTX(FILTER(...)...) or CALCULATE(COUNT(),FILTER(...))

(1) CO-CU Excel to DAX Translation - Microsoft Power BI Community