cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mayur23 Frequent Visitor
Frequent Visitor

COUNTIFS equivalent in Power BI

Hello there,

 

I am trying to write a DAX to get the COUNTIFS equivalent. I have two columns, Order ID and Customer ID as below:

 

Date Order ID Customer ID

 03/22      1           987

 03/22      2           986

 03/23      3             987

03/24        4           986

 

Thus I want another column to be a running count as below (to signify Day 1 of a customer order, Day 2 of customer order etc.)

Date Order ID Customer ID Count

 03/22      1           987          1

 03/22      2           986          1

 03/23      3             987       2

03/24        4           986        3 

 

Thank you for your help. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: COUNTIFS equivalent in Power BI

Hi @mayur23 

Try this for your calculated column, where Table1 is the table you show:

 

ID Count =
CALCULATE (
    COUNT ( Table1[Date] ),
    Table1[Date] <= EARLIER ( Table1[Date] ),
    ALLEXCEPT ( Table1, Table1[Customer] )
)

Do make sure the Date column is actually of type date and not text.

3 REPLIES 3
ccapowerbi Regular Visitor
Regular Visitor

Re: COUNTIFS equivalent in Power BI

Hey @mayur23 !

In the table containing count column,  you have 3 for order id 4 (for customer 986). I suppose that counts the days since order id 2 was placed? It appears the count is the number of days for order id 2, but what about order id 4?

Are you just looking for a count of a numder of days since the customer placed his/her first order? Or are you looking for a count of the number of days for each order?

mayur23 Frequent Visitor
Frequent Visitor

Re: COUNTIFS equivalent in Power BI

Yes sorry, the count for Order ID 4 should be 2. 

 

Also, yes I need the count of the number of days the customer orders and the count should show the corresponding number (a running count).

 

Thank you for your time. 

Super User
Super User

Re: COUNTIFS equivalent in Power BI

Hi @mayur23 

Try this for your calculated column, where Table1 is the table you show:

 

ID Count =
CALCULATE (
    COUNT ( Table1[Date] ),
    Table1[Date] <= EARLIER ( Table1[Date] ),
    ALLEXCEPT ( Table1, Table1[Customer] )
)

Do make sure the Date column is actually of type date and not text.