cancel
Showing results for
Did you mean:
Frequent Visitor

## Count number of Customers, that only have one Record - last 31 days

Hello PowerBi Guru's

A simple (I think) friday struggle 🙂

So, i'm using current Dax-measure to tell me, at all time in total, how many customers has only one record:

COUNTROWS(FILTER(VALUES('RecordTable'[customer_id]),
CALCULATE(COUNT('RecordTable'[Record_id])) = 1)

Now - I want to "filter" that measure, only showing how many of the customers with 1 record - in total(regardless of time) - have bought the last 31 days?.

If I put the filter inside the calculate filterexpression, like this:
COUNTROWS(FILTER(VALUES('RecordTable'[customer_id]),
CALCULATE(COUNT('RecordTable'[Record_id]),
Datesbetween('RecordTable'[Record_time],Today()-31,Today()) = 1)

I will not get the correct result, as the Date-filtration is inside the calculate function, meaning that customers that have more than 1 record (but only 1 last 31 days), will be included.

Tried to add an "&&" after the calculate filter, but it returns an error saying multiple values was return, where a single value is expected.

Any Ideas on how to resolve this?

Has to be a Dax-measure solution - slicer/filter filtration to get the specific result is not an option here.

1 ACCEPTED SOLUTION
Super User

Have you tried that? It works well for me.

If it's possible, share a sample of your data.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

9 REPLIES 9
Super User

Hi @Tegl

Try this measure:

``````Measure =
VAR _D =
TODAY () - 31
VAR _A =
FILTER (
SUMMARIZE (
FILTER ( RecordTable, RecordTable[Record_time] >= _d ),
RecordTable[customer_id],
"Count", COUNT ( RecordTable[Record_id] )
),
[Count] = 1
)
RETURN
COUNTX ( _A, [Count] )``````

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Frequent Visitor

Hi Vahid,

Yours seems to be very close, but still needs a minor adjustment i think.

Your measure returns me how many customers that have 1 record in the last 31days.

But the result I am looking for is, how many customers with 1 record in total (regardless of time) - is in the last 31 days?

=> That will give me a smaller count, as your measure also inlcude those customers that might have more records outside the timeframe(last31days).

So for example 1 customer has 5 records 50 days ago and 1 record 20 days ago. That customer is include in your measure (as I see it) but shouldn't.

Hope it makes sence. 🙂

Super User

Hi @Tegl

Try this:

``````Measure =
VAR _D =
TODAY () - 31
VAR _A =
FILTER (
FILTER (
SUMMARIZE (
RecordTable,
RecordTable[customer_id],
"Count", COUNT ( RecordTable[Record_id] ),
"Max Date", MAX ( RecordTable[Record_time] )
),
[Max Date]
>= TODAY () - 31
&& [Max Date] <= TODAY ()
),
[Count] = 1
)
RETURN
COUNTX ( _A, [Count] )``````

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Frequent Visitor

Hi Again,

sry, but it dosen't seems to be the right approach.

In the first filter from the VAR_A table, you first filter on the dates, which leaves the same issue:

You get a table of customers and records for the last 31 days. Then you filter on the count.

But, I need table (Summarize) of all customers with only one record (regardsless of time) - and then - filter, counting how many of theese have a recordtime in the last 31days?.

(and.. The Max date seems to only bring you one record from each customers, although they might have more).

- Hope it makes sence - thanks for your effort 🙂

Super User

Have you tried that? It works well for me.

If it's possible, share a sample of your data.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Frequent Visitor

Hi Again,

Yes - and found that I made an error implementing it in my file - everything looks great - thanks a lot for you help and effort..

Helper III

I assume your Custoemr is a dimension table. If this is a fixed timeframe (31 days), have you considered adding a calculated column to the Customer table, that counts the number of records in the last 31 days, then set a text value to something like "Activity in Last 31 Days" and "No Activity in Last 31 Days". This new column can then be used to filter your customers.

Also, when using DatesBetween the first parameter is your date table, not the same table that you are counting from. If you don't have a date dimension in your model, you will want to add one.

Hope this helps.

Frequent Visitor

Hi

I like your approach, but not really suitable, as we need a lot of calculations (1 view, 2 views, 3 views etc..)

Tried also by having the First parameter in the Datesbetween from a datetable, but receive same error.

any other suggestions?

Super User

You could tackle this by first getting a list of customers who have one record ever (which you have inside your COUNTROWS).

Next, get a list of customers who have one record in the last 31 days (again you have the basis for this)

Finally you can use INTERSECT to get the customers in both lists and COUNTROWS of that.

So, something like:

Measure =
VAR _alltime = FILTER(...)

VAR _last31days = FILTER(...)

VAR _both = INTERSECT(_alltime, _last31days)

RETURN COUNTROWS(_both)

Announcements