Power BI - countif / averageif with dax

Question

New to DAX. I need a bit of help when moving excel formulas to DAX. I have this table and call it table1:

Date        ID  Time
10/06/2020  1   1
10/06/2020  1   2
11/06/2020  1   3
12/06/2020  2   5
13/06/2020  1   6

And I need to calculate count and average per day per ID. The results with two simple formulas (=COUNTIFS(B:B,B2,A:A,A2)) and (=AVERAGEIFS(C:C,B:B,B2,A:A,A2)) in excel is:

Date        ID  Time    Count ifs   Avergae ifs
10/06/2020  1   1       2           1.5
10/06/2020  1   2       2           1.5
11/06/2020  1   3       1           3
12/06/2020  2   5       1           5
13/06/2020  1   6       1           6

How can I replicate in DAX?

Update

Count in DAX

DAX Count = 
COUNTROWS (
    FILTER (
        'Table1',
        'Table1'[ID] = EARLIER ( 'Table1'[ID] )
            &&  'Table1'[Date] = EARLIER ( 'Table1'[Date] )
    )
)

Sum per day per ID

Sum = 
CALCULATE(
    SUM(Table1[Time]),
    FILTER(
        All(Table1),
        Table1[ID] = EARLIER(Table1[ID]) &&
        Table1[Date] = EARLIER(Table1[Date])
    )
)

Item closed :)

Actually, both and I would need the dynamic changes when displaying the results. — Kalenji
Related: stackoverflow.com/questions/54442875 and stackoverflow.com/questions/52645281 — Alexis Olson
I have count done. DAX Count = COUNTROWS ( FILTER ( 'Table1', 'Table1'[ID] = EARLIER ( 'Table1'[ID] ) && 'Table1'[Date] = EARLIER ( 'Table1'[Date] ) ) ) but need a bit more help with the average. — Kalenji

Kalenji Kalenji · Accepted Answer · 2020-10-06T15:33:48

I have answered the question. @Alexis Olson thank you for the inspiration.

Count in DAX

DAX Count = 
COUNTROWS (
    FILTER (
        'Table1',
        'Table1'[ID] = EARLIER ( 'Table1'[ID] )
            &&  'Table1'[Date] = EARLIER ( 'Table1'[Date] )
    )
)

Sum per day per ID

Sum = 
CALCULATE(
    SUM(Table1[Time]),
    FILTER(
        All(Table1),
        Table1[ID] = EARLIER(Table1[ID]) &&
        Table1[Date] = EARLIER(Table1[Date])
    )
)

Power BI - countif / averageif with dax

1 Answers