Replication of COUNTIF in DAX

[cksmth]

Hi, 

I am trying to replicate the below formula in power BI to number the first event in a squence (or date the new status was changed)

C = Quote Number

E = New Status

G= Date

=COUNTIFS($C:$C,C2,$E:$E,E2,$G:$G,"<"&$G2)+COUNTIFS($C$2:$C2,C2,$E$2:$E2,E2,$G$2:$G2,G2)

Any help is appreciated 🙂

[Kedar_Pande]

@cksmth 

Create a new column:

Event_Number =
VAR CurrentQuote = 'YourTable'[Quote Number]
VAR CurrentStatus = 'YourTable'[New Status]
VAR CurrentDate = 'YourTable'[Date]

RETURN
CALCULATE(
COUNTROWS('YourTable'),
'YourTable'[Quote Number] = CurrentQuote,
'YourTable'[New Status] = CurrentStatus,
'YourTable'[Date] < CurrentDate
)
+ 
CALCULATE(
COUNTROWS('YourTable'),
'YourTable'[Quote Number] = CurrentQuote,
'YourTable'[New Status] = CurrentStatus,
'YourTable'[Date] = CurrentDate,
'YourTable'[Date] <= EARLIER('YourTable'[Date])
)

💌If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Bibiano_Geraldo]

Hi,

Create a new column and paste the following Dax.
Make sure to replace columns and table names with your owns.

if you are satisfied with this answer, please, mark it as solution.

CalculatedColumn =
VAR CurrentQuote = 'YourTable'[Quote Number]
VAR CurrentStatus = 'YourTable'[New Status]
VAR CurrentDate = 'YourTable'[Date]

RETURN
CALCULATE(
COUNTROWS('YourTable'),
FILTER(
'YourTable',
'YourTable'[Quote Number] = CurrentQuote &&
'YourTable'[New Status] = CurrentStatus &&
(
'YourTable'[Date] < CurrentDate ||
(
'YourTable'[Date] = CurrentDate &&
EARLIER('YourTable'[Quote Number]) = CurrentQuote &&
EARLIER('YourTable'[New Status]) = CurrentStatus
)
)
)
)

[rajendraongole1 · Accepted solution]

Hi @cksmth -  you can use the CALCULATE and FILTER functions to count rows based on the conditions specified.

you can replace with your model table name

create a measure as below:

EventNumber =
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[Quote Number] = EARLIER('Table'[Quote Number]) &&
'Table'[New Status] = EARLIER('Table'[New Status]) &&
'Table'[Date] < EARLIER('Table'[Date])
)
) +
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[Quote Number] = EARLIER('Table'[Quote Number]) &&
'Table'[New Status] = EARLIER('Table'[New Status]) &&
'Table'[Date] = EARLIER('Table'[Date])
)
)

Hope this helps.