CODEWITHSUNDEEP
powerbidaxpowerbi-desktopcalculated-columns
WhoIsKi
WhoIsKi

Reputation: 117

Counting distinct IDs for each date in Power BI

I have a dataset and I want to create a column(not measure) to calculate the count of customers in each month. I don't know how I can count each customer once a month in Power BI.

I wrote this code but it counts the number of frequent customers more than once a month.

myCol = CALCULATE( DISTINCTCOUNT('table'[user_id] ) , 'table'[order_date] )

For example, it's my data:

1

The true result should be:

2

but my code returns this result:

3

How should I write the code for this calculating column to get a true result?

Upvotes: 0

Views: 12643

Answers (1)

RADO
RADO

Reputation: 8148

Since you are trying to calculate per month, you need a "year_month" column.

Then:

count_of_customer = 
CALCULATE( 
   DISTINCTCOUNT('table'[user_id]), 
   ALLEXCEPT('table', 'table'[year_month])
)

Result:

enter image description here

Edit:

You don't need a calculated column, you need a measure:

count_of_customer =
    COUNTROWS (
       SUMMARIZE ( 'table', 'table'[year_month], 'table'[user_id])
    )

Upvotes: 1

Related Questions