countifs in powerBI

[Alan_]

Hi i have an excel countifs i want to use in powerbi within a calculted column. 

In the equip column i have:

=COUNTIFS($L$2:[@[machine_serial_number]],[@[machine_serial_number]],$C$2:[@[SITE_NO]],[@[SITE_NO]])

SITE_NO	Equip	machine_serial_number
630	1	06A10260200
630	1	1005100101348
895	1	012011449547
895	1	0895-05
630	1	BH1004191
630	1	K9091550
895	1	KY000961
895	1	9803CL0208
895	2	9803CL0208

What can I use to provide the same result in powerBI?

[Phil_Seamark]

Hi @Alan_,

So do you want a running total of how many times a machine_serial_number exists for any given site?

Do you have an index column or datetime column?

Otherwise it will be tricky to separate the bottom two rows.

[Alan_]

Yes that's it and yes i have a date / time, the table is structured oldest to newest so the count should increase down the table. It's like the rank function but resets based on criteria, in this case the serial and site numbers

[Phil_Seamark]

Hi @Alan_,

So can you please post your data with the datetime.  I can use this to split ties to give a better result.

[Alan_]

Ok here is a better excerpt. In this case the formula in "equip" is:

=COUNTIFS($D$2:[@Serial],[@Serial],$B$2:[@[SITE_NO]],[@[SITE_NO]])

[v-huizhn-msft]

Hi @Alan_,

Have your issue resloved according to @Phil_Seamark posted? It it does, please mark the reight reply as answer. If you doesn't, please feel free to ask.

Best Regards,
Angelia

[Phil_Seamark]

Hi @Alan_

Please try adding this calculated column to your table.  You'll have to rename from Table2 to yours, and comment out the 3rd last line if you need the SITE_NOs to match

Equip = CALCULATE(
            COUNTROWS('Table2'),
            FILTER('Table2',
                'Table2'[Event] <= EARLIER('Table2'[Event])
                && 'Table2'[Serial] = EARLIER('Table2'[Serial])
                --&& 'Table2'[SITE_NO] = EARLIER('Table2'[SITE_NO])
                )
                )